kapil_kk (5/10/2013)
Hi, I have a scenario...I have a table Table1 with columns ID, Names, WorkLocation
I have created a trigger which onupdation insert a data in some audit table...
Update table1
SET Name = ......
WHERE ID = .....
But now I am some another column of this table
Update table1
SET WorkLocation=.......
WHERE ID=.........
But when I do update on this WorkLocation column then also that trigger fire..
How can I prevent to run that Trigger..
That trigger should run when I do update on Name column only...
You would need to check the UPDATE function. http://msdn.microsoft.com/en-us/library/ms187326.aspx
IF UPDATE(Name)
Update table1
SET Name = ......
WHERE ID = .....
Please note that this does not evaluate if the value has actually changed or not. It will return true if the Name column is in the update statement.
If you want to only run your code when the value actually changes you would need to compare inserted and deleted tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/