Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2006-06-20 : 00:12:23
|
| Hi, I have a trigger which is fired after the update is executed. But I want the trigger being fired only when certain column is updated. How do I achieve this in Sql Server?Thanks for any reply.aex |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-20 : 00:40:27
|
| You probably want eitherIF UPDATE ( column ) orIF ( COLUMNS_UPDATED ( ) See Books Online for detailsKristen |
 |
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2006-06-20 : 02:09:07
|
| I think ur expecting the trigger will fire Before inserting the records in inserted table and deleteing the deleted table right..?-- KK |
|
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2006-06-20 : 03:03:01
|
| Thanks Kristen for the sql code. It really help.Now reply to CSK.. actually what happen is my trigger work well when the table is updated. What I mean here is in my table, say there is a column called 'Status', for this column, it accepts two values, i.e. InProgress/Completed. The first time the column is updated, the value is set to 'InProgress' (my trigger start execute here which I do not want it to run first.). What I want is I want the trigger be fired only when the second update is executed when the 'Status' column value is set to 'Completed'.aex |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-20 : 17:11:51
|
Beware that the Trigger will fire once for ALL the rows in the update statement.So, to pick an extereme example, you might doUPDATE MyTableSET Status = CASE WHEN SomeColumn < 5000 THEN 'InProgress' ELSE 'Completed' END so in a single update you will have some rows with the status updated to 'InProgress', and some updated to 'Completed'.Your trigger needs to be prepared to handle that.Kristen |
|
|
|
|
|
|
Trigger problem