Tobar (10/14/2008)
Jack,Thanks for the article. I don't know if it is your cheeky picture or your writing style, but you have become one of my favorites on this forum.
I am very familiar with Oracle triggers and I have not read up on the SQL Server implementation, but you left me with the impression, based on your set examples, that the trigger only fires once for each insert command, versus for every record inserted. I would assume it is for every record inserted, so the need for set stuff is moot. Did I just not get the reasoning behind your example?
Tobar
Tobar,
You had the correct impression. In SQL Server a trigger fires once per DML operation (Insert\Update\Delete). So, if I insert 3 rows in 1 operation the trigger fires once and the virtual inserted table will hold 3 rows and I will need to use set-based operations. Your understanding is why I wrote the article, because most folks new to SQL Server believe a trigger fires once per row, but triggers fire once per operation regardless of the number of rows affected.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question