Set update trigger to keep watch on certain column’s update in SQL Server 2008

Tuesday, January 26, 2010 |

I have seen many times that people keep asking the question how to track the updates in certain column.Well there are several ways to do so but the oldest way is to set UPDATE trigger on the table and keep an eye on the important column.

Please be informed that excesses use of trigger may affect performance so use this method carefully not for keeping track of so many columns in so many tables.

Anyway, let us get back to our core topic and see one small script.

--create one table for demonstration
CREATE TABLE TriggerTest
(
UserName varchar(20),
[Password] varchar(20)
)
GO

--create one table which will LOG information
CREATE TABLE TriggerTestLog
(
ID INT identity(1,1),
UserName varchar(20),
OldPassword varchar(20),
NewPassword varchar(20),
DateOfUpdate datetime
)
GO

--insert some records in first table
INSERT INTO TriggerTest
SELECT 'ritesh','shah' union all
SELECT 'rajan','shah' union all
SELECT 'bihag','goodluck' union all
SELECT 'bhaumik','shruti'
GO

--set after update trigger for first table
--when password will be updated, it will log
--information in second table.
Create TRIGGER TrgTriggerTest ON TriggerTest
   AFTER Update
AS
BEGIN
      SET NOCOUNT ON;
      IF UPDATE([Password])
      BEGIN
            insert into TriggerTestLog(username,OldPassword,NewPassword,dateOfUpdate)
            select i.username,d.[password],i.[password],getdate()
            from inserted i join deleted d on i.username=d.username
      END
END
GO

--select records from second table
--which is blank right now ofcourse
select * from TriggerTestLog
GO

--update first table
UPDATE TriggerTest SET [Password]='furious' WHERE UserName='ritesh'
GO

--you will get information automatically inserted in second table.
select * from TriggerTestLog
GO


Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

0 comments: