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 |
|
Sprinjee
Starting Member
42 Posts |
 Posted - 2006-01-20 : 04:52:37
|
| I want to build a trigger to update a value in the specific row that has just been updated.For example: an article gets update table.status = 1 than that specific row must update table.employee = 'X' but only for this row.I know that it is possible to activate the trigger upon an update in a specific column but I would like to add a clause where id = @id_of_the_row_that_has_actived_the_trigger ;)Really appreciate some help here :D |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-01-20 : 05:00:40
|
| Use if update clauseIF UPDATE(status)BEGINUPDATE <tablename> SET <tablename>.columnname= 'X' from insertedWHERE <tablename>.id = inserted.idEND |
 |
|
|
Sprinjee
Starting Member
42 Posts |
Posted - 2006-01-20 : 05:03:22
|
| yes, but how do I set de "inserted.id" as the id that has activated the triggerAlso my the table doesn't insert a row it only updates |
|
|
|
Sprinjee
Starting Member
42 Posts |
Posted - 2006-01-20 : 05:17:53
|
| maybe I was to quick with my reply seems inserted is a standard table. However the solution does not work. When I look at the name "inserted" I would expect that it takes the id of the last inserted row rather than the last updated row |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-01-20 : 05:18:13
|
| Inserted and Deleted are the two tables mantained by sql server internally. can be accessed only with in a trigger.whenever a row is updated it populate the deleted table and inserted table which contain the record which needs to be updated and what needs to be updated.Inserted.Id is the Id of the row which is actually updated i.e that activated the trigger. you need not to set this.Refer BOL for more info on trigger |
|
|
|
Sprinjee
Starting Member
42 Posts |
Posted - 2006-01-24 : 13:28:02
|
| Thx for your help problem is solved |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-24 : 13:55:38
|
| Probably obvious, but a frequent "Newbie Error": please note that "inserted" and "deleted" can potentially contain multiple rows, and you need to build/design your trigger accordingly.Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-24 : 15:40:00
|
| I would not use the UPDATE check. UPDATE will return true even in cases where the value has not changed, such as:Update YourTable set ColumnA = ColumnAor:Update YourTable set ColumnA = 2 where ColumnA = 2A lot of user interfaces, when sending back a datamodification, send back a value for every column, not just the ones that actually have new values, so this can cause inaccuracies in your data.Better to check: Where inserted.ColumnA <> YourTable.ColumnA if you really want to be sure. Or use BINARY_CHECKSUM() to compare entire records. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-24 : 18:15:40
|
| I would stay away from using BINARY_CHECKSUM() to compare rows because there is a tiny but real possibility that rows that are not identical can have the same checksum.CODO ERGO SUM |
|
|
|
|
|
|
Trigger on specific id