Update/insert Trigger

Hi! I wonder to use conditions in the inserted table (in a insert/update) trigger? The inserted table contain all of the rows that have been updated or inserted (for a update/insert trigger), but out All this is rows in inserted table, I only want the rows where a Particular field have been updated for example if Idkey have Updated it would is in inserted BUT I only want this row if the field Amount have been updated. Can a use of the UPDATE (column) some how? Any Ideas? /jenny June 20 ' 05 # 1

2 Replies for "Update/insert Trigger"

p:n/a Ray Higdon Do your have a primary key on the table that does don't change? If So, this Would work, can change the flag= ' y ' to whatever you want the Trigger to do if the idkey changes. Not tested, and this takes to account multiple updates can being done in One time. Create trigger Foo on bar For update As If Update (Idkey) Update bar Set flag = ' Y ' where idkey in (select A.idkey from Bar a join deleted D On a.pk = d.pk where A.idkey!= d.idkey) Hth Ray Higdon MCSE, MCDBA, CCNA Sent via Developersdex http://www.developersdex.com * * * Don ' t just participate in Usenet...get rewarded for it! June 20 ' 05 # 2

p:n/a Erland Sommarskog [posted and mailed, Vänligen Svara I NYS] Jenny (jenny.karlsson@spray.se) Writes:[color=blue] > I wonder how to use conditions in the inserted table (in a > Insert/update) trigger? The inserted table contain all of the rows that > have been updated or inserted (for a update/insert trigger), but out > All this is rows in inserted table, I only want the rows where a > Particular field have been updated for example if Idkey have > updated it would be in inserted BUT I only want this row if the field > Amount have been updated. Can a use of the UPDATE (column) some how? Any > Ideas? [/color] UPDATE (column) only tells the "column was" on "the left hand Side of the SET clause in the UPDATE statement. It does say anything About whether the value was changed. Instead, you simply have to compare the tables: SELECT * into #tbl_inserted from inserted SELECT * into #tbl_deleted from deleted SELECT i.*, D.col From #tbl_inserted I JOIN #tbl_deleted D on i.keycol = D.keycol WHERE I.yourcol <> D.yourcol The point with the temp tables, are that the virtual tables "inserted" and "deleted" can be slow, not the least if your include both in the Same query. -- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at Http://www.microsoft.com/sql/techinf...2000/books.asp