Posted by Erland Sommarskog on 07/21/05 01:07

(wackyphill@yahoo.com) writes:
> Wow, that's really excellent stuff. Thanks so much for explaining it
> out.
> I understood what you said except for EXISTS (SELECT * FROM
> inserted...) being cleaner. I don't follow where that could be used
> instead of a join. That's just going to be true all the time isn't it?

Nah, the WHERE clause was implied:

UPDATE tbl
SET datecol = convert(char(8), t.datecol, 112)
FROM tbl t
WHERE EXISTS (SELECT *
FROM inserted i
WHERE t.keycol1 = i.keycol1
AND t.keycol2 = i.keycol2
... )

Here I have the FROM clause only to be able to use an alias.

And, by the way, since this is a commen misunderstanding, I should say
that in SQL Server triggers fires once per *statement*. It's a common
mistake to assume that triggers fires once per row, which lures people
to write triggers that handles multi-row operation properly.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]