SQL Server Performance Forum – Threads Archive

help on trigger

Hi all, I am totally confuse on this trigger issues. I have table test. now i have to keep track of changes in columns in this table the column need to be track is Key,timechanger, field change,fieldname(such as salary), valuechanged, datetime How can i create trigger to do this. please help thanks Thanks!!

You have to spell it out for each column that you want to monitor … Other than that, just look at the basic features of triggers, like the UPDATE() function: IF UPDATE(<column&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> BEGIN ……… END

hi all i want to keep track of changes for the column and when they change then want to keep track in different table where it lists the id(key),fieldname,timeof changes, user,and value thanks
` Thanks!!

Like I said, the IF UPDATE(<COLUMN&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> clause will tell you that an update action has been undertaken on the column that you mention. The test for @@ROWCOUNT is there in case there is a cascading delete on an FK relationship for this table. If the cascading update occurs, @@ROWCOUNT in an UPDATE trigger is 0. The final bit you need to understand is the "inserted" conceptual table – it’s a snapshot of only the affected rows in the table after the update. There is also one called "deleted", which is a snapshot of only the affected rows in the table before the update. CREATE TRIGGER Test_UpdateTrigger ON dbo.TestTable FOR UPDATE AS BEGIN IF @@ROWCOUNT > 0 BEGIN IF UPDATE(TestColumn1) BEGIN INSERT INTO dbo.LogChanges (id, fieldname, time, user, value) SELECT inserted.id, ‘TestColumn1’, GETDATE(), SUSER_SNAME(), inserted.TestColumn1 FROM inserted END IF UPDATE(TestColumn2) BEGIN INSERT INTO dbo.LogChanges (id, fieldname, time, user, value) SELECT inserted.id, ‘TestColumn2’, GETDATE(), SUSER_SNAME(), inserted.TestColumn2 FROM inserted END END END In case you want to ignore re-entry of the same value, the INSERT INTO needs a little extra work: INSERT INTO dbo.LogChanges (id, fieldname, time, user, value) SELECT inserted.id, ‘TestColumn1’, GETDATE(), SUSER_SNAME(), inserted.TestColumn1 FROM inserted INNER JOIN deleted ON inserted.id = deleted.id WHERE ISNULL(inserted.TestColumn1, ”) <> ISNULL(deleted.TestColumn1, ”) etc. etc. This type of trigger is never short, sweet or simple.

]]>