Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger effect Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 8:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1451623
Posted Friday, May 10, 2013 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 13,081, Visits: 12,545
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1451624
Posted Friday, May 10, 2013 6:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1451792
Posted Saturday, May 11, 2013 3:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 40,182, Visits: 36,587
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451820
Posted Tuesday, May 14, 2013 4:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1452493
Posted Tuesday, May 14, 2013 5:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 40,182, Visits: 36,587
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1452502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse