Wednesday, February 29, 2012

Trigger

Use CommunityAssist
--trigger to flag all donations above 1000 
--to be checked out by somebody
--will write them into a temp table
-- for or after trigger lets the operation
--occur and then fires the trigger
--instead of triggers intercept the operation
--and do the content of the trigger instead of
--the insert, update or delete
Go
--triggers are usually on a particular table
Create trigger tr_BigDonations on Donation
for insert --this will fire with every insert
As --beginning of the body of the trigger
If not Exists --check to see if our temp table exists
 (Select name from sys.tables
   where name = 'BigDonations')
Begin --begin of the if block
--if the table doesn't exist create it
Create table BigDonations
(
 DonationKey int, 
 DonationDate datetime, 
 DonationAmount money, 
 PersonKey int, 
 EmployeeKey int
)

End --end of if exists block
--create a variable
Declare @DonationAmount money
--set a value for the variable based
--on the inserted table (a temp table that 
--keeps all insertions for the duration of
--the transaction (less than a nano second)
--there is also a deleted table that keeps
--updates and deletes
Select @DonationAmount=DonationAmount 
   From Inserted
--check the value of the donation   
If @DonationAmount >= 1000
Begin --begin if
--if it is 1000 or larger insert it also
--into the BigDonations Table
Insert into BigDonations (DonationKey, 
DonationDate, 
DonationAmount, 
PersonKey, 
EmployeeKey)
Select DonationKey, 
DonationDate,  
DonationAmount, 
PersonKey, 
EmployeeKey
From Inserted
End --end if condition
--also end of trigger

--Test the trigger 
Insert into Donation (DonationDate, 
DonationAmount,
 PersonKey, 
 EmployeeKey)
Values(GETDATE(), 1002,1,1) 

--should be inserted into donation
Select * from Donation

--should also be inserted into Bigdonations
Select * from BigDonations

--this stored procedure will also fire the trigger
Exec usp_NewDonation
@lastname ='Lawrence', 
@firstname='Bob', 
@street='10000 nowhere', 
@city='seattle', 
@state='wa', 
@zip='98100', 
@homephone='2065551111', 
@email='boblawrence@yahoo.com', 
@donationAmount=1500.96


No comments:

Post a Comment