Triggers and multiple rows updated

[webuser]

From what i understand, triggers fire when a row or rows are updated. So if a user updates one row, the trigger will fire once. What happens when say, a stored procedure updates multiple rows? Does the trigger fire once for each row, or does it fire one time, populating the Insert and Deleted tables? I have tried some experimenting and it seems like it does the latter, b/c I definitely saw multiple rows in the inserted/deleted tables, but at other times, it seems like it only has one row. I'm confused! Any explanation would be really helpful! Thanks!

 

[vishnuprasad]

U have got the it right when u say that trigger fires only once irrespective of the number of updates/inserts. This is because the execution statements are logged in transaction logs and are moved into durable state in the DB tables only when the trigger fires with success. So it does not matter 1 or n rows are updated, trigger handles all rows affected with the same logic. If this fails all these entries in log is rolled back.Hence we have a control over the updated rows using "inserted" and "deleted" statements.

In Oracle we have the option of specifying that triggers fire for each row updates with "for each row " option.

Hope this solves u'r doubt.