By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,619 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,619 IT Pros & Developers. It's quick & easy.

Update/insert trigger

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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!
Jul 20 '05 #2

P: n/a
[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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.