Trigger

Use CommunityAssist
Go
Create trigger tr_MaxGrant on ServiceGrant
instead of Insert
As
Declare @amount money
Declare @max money
Declare @ServiceKey int

Select @ServiceKey=serviceKey, @Amount=GrantAmount
From Inserted 
Select @max=ServiceMaximum from CommunityService
Where ServiceKey = @ServiceKey
If @Amount <= @Max
Begin
Insert into ServiceGrant(
GrantAmount, GrantDate, PersonKey, ServiceKey)
Select GrantAmount, GrantDate, PersonKey, ServiceKey
From Inserted
End
Else
Begin
if not exists
 (Select name from sys.tables where name ='Rejects')
 Begin
  Create Table Rejects
  (
   PersonKey int,
   ServiceKey int,
   GrantDate Datetime,
   GrantAmount money,
   ServiceMaximum money,
   GrantDiff money
  )
 End
Insert into Rejects (PersonKey, ServiceKey, GrantDate, GrantAmount, 
ServiceMaximum, GrantDiff)
Select PersonKey, ServiceKey, GrantDate, GrantAmount, @max, GrantAmount-@max
From inserted

End

Select * from ServiceGrant

Select * from CommunityService

Insert into ServiceGrant([GrantAmount],[PersonKey],[GrantDate],[ServiceKey])
Values (300.00, 4, GetDate(), 4)

Select * From ServiceGrant where Personkey =4

Select * from Rejects