Tuesday, July 04, 2006

Merge triggers

Had a quick puzzler at work today that I thought I'd share... and the link to Ask Tom that solved it (before I could be bothered to delve into the test case myself).

Q: Which statement level triggers fires on a MERGE statement?

A: Since it is possible for either inserts or updates to occur during the statement, and since statement level triggers always fire even when no rows are processed, both the INSERT and UPDATE triggers fire every time, regardless of whether any inserts or updates occur.

Obvious when you think about it. Trouble is, how many our YOUR statement level triggers would work properly if both sets fired at the same time? There's no reason why they shouldn't, if you code for the possibility....

Tom (and Mikito and Kevin and others) explains here.

1 comment:

Anonymous said...

Now the question is, in which order do they fire? All INSERT first, then all UPDATEs? Or all UPDATES, then INSERTs? Or all random? Documented and undocumented answers, of course. :)