Update/insert trigger

[Jenny]

Hi!

I wonder how to use conditions in the inserted table(in a
insert/update) trigger? The inserted table contain all the rows that
have been updated or inserted (for an update/insert trigger), but out
of all these rows in inserted table, I only want the rows where a
particular field have been updated, for example if idkey have been
updated it would be in inserted BUT I only want this row if the field
amount have been updated. Can a use the UPDATE(column) some how? Any
ideas?
/Jenny

[Ray Higdon]

Do you have a primary key on the table that does not change? If so, this
would work, you can change the flag= 'y' to whatever you want the
trigger to do if the idkey changes.

Not tested, and this takes into account multiple updates can be done at
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!

[Erland Sommarskog]

[posted and mailed, vänligen svara i nys]

Jenny (je************@spray.se) writes:

I wonder how to use conditions in the inserted table(in a
insert/update) trigger? The inserted table contain all the rows that
have been updated or inserted (for an update/insert trigger), but out
of all these rows in inserted table, I only want the rows where a
particular field have been updated, for example if idkey have been
updated it would be in inserted BUT I only want this row if the field
amount have been updated. Can a use the UPDATE(column) some how? Any
ideas?

UPDATE(column) only tells you that 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, is that the virtual tables "inserted"
and "deleted" can be slow, not the least if you include both in the
same query.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp