Triggering
Concept Trigger that will run when someone updates the SalaryGrade within the specified table automatically the trigger will capture the previous salarygrade and insert into another table along with New and previous salarygrade..
--- Table 1 : Employee
Create Table Employee (
EmployeeID int not null
Constraint pk_Employee_EmployeeID primary key,
EmployeeName varchar(10) not null,
SalaryGrade varchar(10) not null,
Slary smallmoney not null
)
-------------------------------------------------------------
--- Table 2 : EmployeeChanges
Create Table EmployeeChanges (
ChangeID int identity(1,1) not null
Constraint pk_EmployeeChanges_ChangeID primary key,
ChangeDateTime datetime not null,
EmployeeName varchar(10) not null,
OldSalaryGrade varchar(10) not null,
NewSalaryGrade varchar(10) not null,
)
--------------------------------------------------------------
--- Before Update Insert to Another Table
CREATE Trigger tr_Update_RecordSalaryGradeChanges
On Employee
For Update As
declare @rowcount as int
declare @ChangeDateTime as datetime
declare @EmployeeName as varchar(10)
declare @OldSalaryGrade as smallmoney
declare @NewSalarygrade as smallmoney
If @@RowCount > 0
Begin
If update (Salarygrade)
Begin
begin transaction
select @ChangeDateTime = getdate(),
@EmployeeName = Inserted.EmployeeName,
@OldSalaryGrade = Deleted.Salarygrade,
@NewSalaryGrade = Inserted.Salarygrade
from Inserted inner join Deleted
on Inserted.EmployeeID = Deleted.EmployeeID
insert EmployeeChanges(ChangeDateTime, EmployeeName, OldSalaryGrade, NewSalarygrade)
values (@ChangeDateTime, @EmployeeName, @OldSalaryGrade, @NewSalarygrade)
set @rowcount = @@rowcount
if @@rowcount = 1
Begin
Commit transaction
End
Else
Begin
Rollback transaction
RaisError('Error recording to change table, contact admin', 16, 1)
End
End
End
Return
--- drop trigger tr_Update_RecordDeliveryChargeChanges
----------------------------------------------------------------
--- For Multiple Records Update in Employee Tables
Alter Trigger tr_Update_RecordSalaryGradeChanges
On Employee
For Update As
If update (salarygrade)
Begin
Insert EmployeeChanges(ChangeDateTime, EmployeeName, OldSalaryGrade, NewSalaryGrade)
Select getdate(), Inserted.EmployeeName,Deleted.SalaryGrade,Inserted.SalaryGrade
From Inserted inner join Deleted
on Inserted.EmployeeID = Deleted.EmployeeID
If @@error <> 0
Begin
RaisError('Error recording to change table, contact admin', 16, 1)
End
End
----------------------------------------------------------
--- For mail send while Update in Employee Table
/*
To ensure before this trigger configure your database mail.
for more information please visit db mail configuration Page :
http://sqlservertechnics.blogspot.com/2010/03/db-mail-configuration.html
*/
ALTER TRIGGER EmployeeUpdateMail
-- and the table is CustomerInfo
ON Employee
-- trigger is fired when an update is made for the table
FOR UPDATE
AS
-- holds the EmployeeID so we know which Customer was updated
declare @EmployeeID varchar(10)
-- holds the body of the email
declare @body varchar(2000)
-- holds the old customer name which has been changed
declare @EmployeeName varchar(10)
-- holds the new customer name
declare @EmployeeNewName varchar(10)
-- gets the previous customer first name that was deleted
SELECT @EmployeeID = EmployeeID,
@EmployeeName = d.EmployeeName
FROM deleted d
-- gets the new customer first name
SELECT @EmployeeNewName = EmployeeName
FROM inserted
SET @body = 'Employee with ID= ' + @EmployeeID + ' has been updated
with previous Employee Name is : ' + @EmployeeName + 'and the
New Employee Name is : '+ @EmployeeNewName
--- xp_sendmail is the extended sproc used to send the mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Email',
@recipients = 'adscxdasf@dsfasdf.com',
@body = @body,
@subject = 'Employee Information Updated'
GO
-----------------------------------------------------------
-- Workouts
Select * from dbo.Employee
Select * from dbo.EmployeeChanges
--- delete from dbo.EmployeeChanges
update Employee set salarygrade='FinalMail'
where EmployeeID in (1,2)
update Employee set EMPLOYEENAME='FinalMail5'
where EmployeeID in (1)
-----------------------------------------------------------
