I want to know what happens with triggers when I run this <upsert>.
Let's say, my <upsert> updates 3 rows and inserts another 2.
Let's say, I have different triggers after insert and after update.
Will both of them fire ?
I would imagine that with FOR EACH ROW triggers that the correct trigger would fire, otherwise the trigger code would be illegal. Ie, in an after insert trigger, there are no :OLD values, and referencing them would cause an error. So certainly for these types of triggers I would imagine they have to fire correctly.
For triggers fired after the whole operation, I'm not sure but I would think it isn't quite as important as with "for each row" triggers, as in a traditional bulk insert/update trigger you don't know which specific rows were touched anyway?
The only thing I can suggest is to create a couple and find out? This begs the question is there a new trigger firing type called "after upsert on..." :-)
MERGE INTO sales s
USING new_sales n
ON (s.sales_transaction_id = n.sales_transaction_id)
WHEN MATCHED THEN
UPDATE s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar
WHEN NOT MATCHED THEN
INSERT (sales_quantity_sold, sales_dollar_amount)
VALUES (n.sales_quantity_sold, n.sales_dollar_amount);