help on trigger | SQL Server Performance Forums

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 …<br /><br />Other than that, just look at the basic features of triggers, like the UPDATE() function:<br /><br />IF UPDATE(&lt;column&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br />BEGIN<br />………<br />END<br />
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(&lt;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.<br /><br />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.<br /><br />The final bit you need to understand is the "inserted" conceptual table – it’s a snapshot of only the affected rows in the table <i>after</i> the update. There is also one called "deleted", which is a snapshot of only the affected rows in the table <b>before</b> the update.<br /><br /><br />CREATE TRIGGER Test_UpdateTrigger ON dbo.TestTable<br />FOR UPDATE<br />AS<br />BEGIN<br /><br />IF @@ROWCOUNT &gt; 0<br />BEGIN<br /><br />IF UPDATE(TestColumn1)<br />BEGIN<br /><br />INSERT INTO dbo.LogChanges (id, fieldname, time, user, value)<br />SELECT inserted.id, ‘TestColumn1’, GETDATE(), SUSER_SNAME(), inserted.TestColumn1<br />FROM inserted<br /><br />END<br /><br /><br />IF UPDATE(TestColumn2)<br />BEGIN<br /><br />INSERT INTO dbo.LogChanges (id, fieldname, time, user, value)<br />SELECT inserted.id, ‘TestColumn2’, GETDATE(), SUSER_SNAME(), inserted.TestColumn2<br />FROM inserted<br /><br />END<br /><br />END<br /><br />END<br /><br /><br /><br />In case you want to ignore re-entry of the same value, the INSERT INTO needs a little extra work:<br /><br />INSERT INTO dbo.LogChanges (id, fieldname, time, user, value)<br />SELECT inserted.id, ‘TestColumn1’, GETDATE(), SUSER_SNAME(), inserted.TestColumn1<br />FROM inserted INNER JOIN deleted ON inserted.id = deleted.id<br />WHERE ISNULL(inserted.TestColumn1, ”) &lt;&gt; ISNULL(deleted.TestColumn1, ”)<br /><br />etc. etc.<br /><br />This type of trigger is never short, sweet or simple.
]]>