May 10, 2013 at 8:26 am
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...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 10, 2013 at 8:30 am
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/
May 10, 2013 at 6:55 pm
But how can we stop the trigger from being runned when updation is performing on some another column of a table
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 11, 2013 at 3:39 am
With the IF UPDATE construct that Sean gave you. The trigger will still run, but the IF will return false and hence the code inside won't run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2013 at 4:41 am
Can you guys please provide some more details on it....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 14, 2013 at 5:00 am
Why don't you write some code, play with it and see how it works (and see the relevant sections in Books Online)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply