triggers

--triggers
--stored procedure triggered by an event
--insert, update, delete
--for, after, instead of
-- for and after let the event happen and then
--execute the trigger code
--instead of intercepts the event and does
--the trigger code instead of the event
Create trigger tr_BigDonation on Donation
for insert 
As
if not exists
  (Select name from Sys.Tables
  Where name='BigDonations')
Begin
Create table BigDonations
(
 DonationKey int, 
 DonationDate DateTime, 
 DonationAmount money, 
 PersonKey int
)
End
Declare @DonationAmount money
Declare @Threshold money =500
Select @DonationAmount = DonationAmount
From Inserted
if @DonationAmount >= @Threshold
Begin
Insert into BigDonations(DonationKey,DonationDate,DonationAmount,
PersonKey)
Select DonationKey, DonationDate, DonationAmount,
PersonKey from Inserted
End

Select * from Person
Insert into Donation(DonationDate, DonationAmount, PersonKey)
Values(GetDate(), 550.75, 136)

Select * from Donation
Select * from BigDonations
Go
--Here is an alternate, more efficent version of that trigger
Create trigger tr_BigDonation on Donation
for insert 
As
--declare variables
Declare @DonationAmount money
Declare @Threshold money =500
--get value from temporary Inserted table
Select @DonationAmount = DonationAmount
From Inserted
--if the donation amount is 500 or above
if @DonationAmount >= @Threshold
Begin --outer if
if not exists
  (Select name from Sys.Tables
  Where name='BigDonations')
Begin--inner if
Create table BigDonations
(
 DonationKey int, 
 DonationDate DateTime, 
 DonationAmount money, 
 PersonKey int
)
End --end inner if
Insert into BigDonations(DonationKey,DonationDate,DonationAmount,
PersonKey)
Select DonationKey, DonationDate, DonationAmount,
PersonKey from Inserted
End --end outer if

go

Alter trigger tr_NoDelete on Donation
instead of Delete, update
As
  if not exists
     (Select name from Sys.Tables
      Where name='DeletedDonations')
   --if not create it
       Begin 
           Create table DeletedDonations
            (
           DonationKey int, 
           DonationDate DateTime, 
           DonationAmount money, 
           PersonKey int
             )    
       End
   Insert into DeletedDonations         (DonationKey,DonationDate, 
   DonationAmount, PersonKey)
 Select DonationKey, DonationDate, DonationAmount,
 PersonKey from Deleted



--
Delete from Donation where donationKey=47

Select * From Donation

Select * From DeletedDonations
go
--disable trigger to allow deletions
Disable trigger tr_NoDelete on Donation
go
--re enable trigger
enable Trigger tr_NoDelete on Donation