Triggers

--Triggers like stored proc
--except they are triggered by an event
--insert, update, delete
--enforcing business
--every grant request should be reviewed within 2 days
--after, instead of

use Community_Assist
go
Create Trigger tr_ReveiwList on GrantRequest
after insert 
As
--check to see if table exists
if not exists
   (Select name from sys.tables
      where name='ToReview')
Begin
  Create Table ToReview
  (
      GrantRequestKey int,
   GrantRequestDate datetime,
   DateToReviewBy Date
  )
end
Declare @GrantKey int
Declare @GrantRequestDate Datetime
Declare @DateToReview Date
Select @GrantKey=GrantRequestKey, 
@GrantRequestDate=GrantRequestDate
From Inserted
Set @DateToReview=dateAdd(Day,2,@GrantRequestDate)
Insert into ToReview(GrantRequestKey, GrantRequestDate, DateToReviewBy)
Values(@GrantKey, @GrantRequestDate, @DateToReview)
go

Insert into GrantRequest(GrantRequestDate, PersonKey, GrantTypeKey, 
GrantRequestExplanation, GrantRequestAmount)
Values(GetDate(),4,1,'Hungry this morning',60)

Select * from toReview


Select * from BusinessRule

Go
Create trigger tr_CheckMaximum on GrantRequest
instead of insert
As
Declare @requestAmount money
Declare @MaxRequestAmount money
Declare @GrantType int

Select @RequestAmount=GrantRequestAmount, 
       @GrantType=GrantTypeKey
    From inserted

Select @MaxRequestAmount=GrantTypeMaximum 
       From GrantType
    Where GrantTypekey = @GrantType

If @RequestAmount > @MaxRequestAmount
Begin
       print 'Request exceeds maximum allowed'
    return
End
Else
Begin
  Insert into GrantRequest(GrantRequestDate, PersonKey, 
  GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
  Select  GrantRequestDate, PersonKey, GrantTypeKey, 
  GrantRequestExplanation, GrantRequestAmount from inserted
End

Insert into GrantRequest(GrantRequestDate, PersonKey, 
  GrantTypeKey, GrantRequestExplanation, GrantRequestAmount)
  Values(GetDate(), 6, 1, 'Need Food', 100.75)


  Select * from GrantType
Select * from GrantRequest
Select * from ToReview