Triggers

use CommunityAssist

--trigger
--procedures that fire when an event occurs
--most common: insert update and delete
--used to enforce business rules
--for, after, instead of
go
-- this trigger checks to see if a donor 
--donates more than a 1000 dollars
--if they do it will write their donation
--to a table call bigDonations for a follow
--up later
Create trigger tr_HighDonation on Donation
for insert,update --works on both insert and update
As
Declare @donationAmount money
--get the value of the donation from the temporary
--table "inserted"
Select @donationAmount = donationAmount
from inserted
--test the value 
If @donationAmount >= 1000
Begin
    --if the table doesn't exist create it
 if not exists
  (Select name from sys.tables where name='bigDonations')
 Begin
  Create table bigDonations
  (
   DonationKey int, 
   DonationDate dateTime, 
   DonationAmount money, 
   PersonKey int, 
   EmployeeKey int
  )
 end
 --insert into the table bigDonations
 --from the temporary inserted table
 Insert into bigDonations(DonationKey, 
 DonationDate, 
 DonationAmount, 
 PersonKey, EmployeeKey)
 Select DonationKey, DonationDate, 
 DonationAmount, PersonKey, 
 EmployeeKey from Inserted
End

--insert to test the trigger
--this goes into the bigDonation Table
Insert into Donation(DonationDate, DonationAmount, 
PersonKey, EmployeeKey)
Values(GETDATE(), 1002,1,1)

--this doesn't
Insert into Donation(DonationDate, DonationAmount, 
PersonKey, EmployeeKey)
Values(GETDATE(), 999,63,1)

Select * From donation

Select * from bigDonations
 
Go
--an instead of trigger intercepts the event and
--does its commands instead of the eve t
Create Trigger tr_NoDelete on Donation
instead of delete
as
print 'You can''t delete from this table'

go
--test the instead of trigger
Delete from Donation where DonationKey=3
Delete From Donation