Search

Custom Search

Saturday, March 8, 2008

Adding triggers for your Table

Triggers are very useful specially on registering audits on changes on tables.
Triggers has INSERTED and DELETED object that can be used to get the activity
made on the table and be able to save it on your audit tables.
These objects has the same schema as your table so you can join it with other table to get other information you needed

Below is the syntax in adding trigger on your table and how to save it on your audit.

CREATE TRIGGER [Trigger_all] ON [dbo].[SampleTable]
For INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
BEGIN
--procedure for Update
INSERT INTO SampleTable_Audit VALUES(SELECT *, 'EDITTED', timestamp() FROM DELETED)
END
ELSE IF EXISTS(SELECT * FROM INSERTED)
BEGIN
--Procedure for insert
INSERT INTO SampleTable_Audit VALUES(SELECT *, 'Inserted', timestamp() FROM INSERTED)
END

ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
--Procedure for delete
INSERT INTO SampleTable_Audit VALUES(SELECT *, 'EDITTED', timestamp() FROM DELETED)
END


No comments:

Post a Comment

Adsense Banner