Triggers

Use Community_Assist
go
Create trigger tr_ChangeEmployeePosition on EmployeePosition
after Update
as
Declare @employeeKey int
Declare @EmployeeAnnualSalary money
Select @EmployeeKey = Employeekey from Deleted
Select @EmployeeAnnualSalary=EmployeeAnnualSalary from Employee
where Employeekey = @EmployeeKey
if(@EmployeeAnnualSalary=0)
Begin
Update Employee
Set EmployeeAnnualSalary =1000
where EmployeeKey = @EmployeeKey
end
Else
Begin
Update Employee
Set EmployeeAnnualSalary=EmployeeAnnualSalary*1.05
Where employeeKey = @employeeKey
End

Select * from EmployeePosition

Update EmployeePosition
Set PositionKey = 3
where employeeKey=4

Go
Alter trigger tr_DeletePerson on Person
instead of delete
As
if not exists 
  (Select name from sys.Tables 
   Where name ='Deletions')
Begin
 Create table Deletions
 (
 PersonKey int, 
 PersonLastName nvarchar(255), 
 PersonFirstName nvarchar(255), 
 PersonEmail nvarchar(255),  
 PersonEntryDate datetime
 )
End
Insert into Deletions(PersonKey, PersonLastName, PersonFirstname,
PersonEmail, PersonEntryDate)
Select PersonKey, PersonLastName,PersonFirstName,
PersonEmail, PersonEntryDate from Deleted

Select * from Person
Delete from Person where Personkey=133
Select * from Deletions

Alter table Person
disable trigger tr_DeletePerson

Alter table Person
enable trigger tr_DeletePerson

Select * from BusinessRule

--Create a trigger that gives the date
--by which a grant request should be reviews
go
Create trigger tr_GrantReviewDateDue
on GrantRequest
after Insert
As
if not exists
 (Select name from sys.tables 
   Where name = 'ToReview')
 begin
  Create table ToReview
  (
     GrantRequestKey int,
     GrantRequestDate DateTime,
     GrantTypeKey int,
     ToReveiwByDate Datetime
  )
 end
 Insert into ToReview(GrantRequestKey, GrantRequestDate,
 GrantTypeKey, ToReveiwByDate)
 Select GrantRequestKey, GrantRequestDate, GrantTypeKey, 
 DateAdd(hour,48,GrantRequestDate)
 From inserted

 Insert into GrantRequest( GrantRequestDate, PersonKey, GrantTypeKey, 
 GrantRequestExplanation, GrantRequestAmount)
 Values(GetDate(),121,1,'didn''t eat a good breakfast',200.00)

 Select * from GrantRequest
 Select * from ToReview