Triggers

-- triggers
--this is a very simple trigger
--it activates on inserts and updates to the 
--person table, but does nothing but
--print out a statemet
Create trigger tr_doNothingTrigger
on Person
after Insert, Update
As
Print 'there was an insert'

--test it
Insert into Person(lastName, Firstname)
Values('Brown', 'John')

Update Person
Set LastName='Browning'
Where Firstname='John'
and Lastname='Brown'
go

--this trigger prevents any deletion
--from the Donation table
--instead of triggers intercept the action
--and do what is in the body of the trigger
--instead of the command
Create trigger tr_DonationDelete
on Donation
instead of Delete
As
Print 'You are not allowed to Delete'



Delete from Donation where DonationKey=3



--to remove this trigger
Drop trigger tr_donationDelete


--just to see who has high total grant amounts
Select personkey, sum(GrantAmount)
From ServiceGrant
Group by Personkey

--business rule will be that the maximum lifetime grant is 2000
-- on an insert into the ServiceGrant
--if their total grants are greater than 2000, 
--then write their grant into a temp table, showing their total grants and
--how much is still available if any
--if their total is less than 2000 with the new grant then just insert it
go
--**************************************************
Create trigger tr_LifeTimeGrant
on ServiceGrant
instead of Insert --will intercept insertions
As
--delaring variables
Declare @PersonKey int
Declare @TotalGrant money
Declare @Grant money
Declare @maxGrant money
Declare @RemainingGrant money
--settin values
set @maxGrant=2000
--these values come from the temporary table
--inserted that exists for as long as the insert
--transaction is open (a millisecond or less)
--but you can access it in a trigger
Select @Personkey=personkey, @Grant=GrantAmount
from Inserted

--get the totals from the GrantService Table
Select @TotalGrant=Sum(GrantAmount) 
From ServiceGrant
Where personKey=@Personkey

--check to see if the total plus the current grant
--are greater than the allowed maximum grant
if (@TotalGrant + @Grant >= @maxGrant)
Begin
  --if it is make sure the tempGrant table
  --exists. If not make it
  if not exists
   (Select name 
   from sys.Tables 
   where name = 'TempGrant')
   Begin
    Create table TempGrant
    (
     PersonKey int,
     GrantAmount money,
     TotalGrant money,
     AvailableGrant money

    )
   End
   
  --table made set the remaining grant amount
   Set @RemainingGrant=@maxGrant-@TotalGrant
   --insert into the tempGrant table
   Insert into TempGrant 
   values(@PersonKey, @Grant, @TotalGrant, @RemainingGrant)
End --end the of
Else --begin the else
Begin
--if it is not larger than the maximum alloted value 
--just insert it into the service grant table
Insert into ServiceGrant(
GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Select GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey
From Inserted
End
--****************************************
--test the trigger

Insert into ServiceGrant(GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Values(950,getDate(),16,3,1)



Select * from TempGrant

Drop table TempGrant