Menu
I need to know about how the trigger execution happens for the below scenario.
I have 20 records in a table and I have an AFTER INSERT, UPDATE trigger on that table. When I'm updating all the records in that table using a MERGE or batch update statement, how will the trigger execute?
Does it execute for each row by row?
Or is it executing once per a batch (once for all 20 records)?
If it is execute once per batch do we need to write a loop inside the trigger to perform a task for each row?
751
You can create multiple triggers for the same subject table, event, and activation time. The order in which those triggers are activated is the order in which the triggers were created. Db2 records the timestamp when each CREATE TRIGGER statement executes.
AFTER UPDATE Trigger is a kind of trigger in SQL that will be automatically fired once the specified update statement is executed. It can be used for creating audit and log files which keep details of last update operations on a particular table.
However, you can write a CTE inside a stored procedure or User Defined Functions (UDFs) or triggers or views. However, you cannot implement CTEs inside indexed views.
In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.
Triggers in SQL Server always execute once per batch - there's no option for "for each row" triggers in SQL Server.
When you mass-update your table, the trigger will receive all the updated rows at once in the inserted and deleted pseudo tables and needs to deal with them accordingly - as a set of data - not a single row
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
