Pages

Saturday, December 10, 2011

How to create trigger on mssql

In mssql we can create a trigger to get the orginal values in a separate table with the same structure to track changes on original table. It can be done using both trigger after insert, after update or after delete.
Below is scripts to create trigger in mssql :
Trigger insert
CREATE TRIGGER employee_aft_ins ON employee
AFTER INSERT 
AS
INSERT INTO employee_log
SELECT ID,FirstName,LastName FROM INSERTED
Trigger Update
CREATE TRIGGER employee_aft_upd ON employee
AFTER UPDATE
AS
INSERT INTO employee_log
SELECT ID,FirstName,LastName FROM DELETED
Trigger Delete
CREATE TRIGGER employee_aft_del ON employee
AFTER DELETE
AS
INSERT INTO employee_log
SELECT ID,FirstName,LastName FROM DELETED
Notes
Replace tables and fields name with your own
employee is the original table, while employee_log is table to save log history of original table

No comments:

Post a Comment

Don't Forget To Join Our Community
×
Widget