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
Wednesday, February 29, 2012
Trigger
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment