Menu
I have created an on update trigger.
If I am updating 5 records in the table in a single statement, how many times will the trigger execute? Does it change if I have multiple statements updating the records in a single transaction?
Does it execute 5 times, or only once after all the transactions are complete?
262
In most cases if you update 10 rows trigger will be fired 10 times.
Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the Customer table, then a statement-level trigger on that table would only be executed once.
An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.
Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.
It all depends on the type of trigger you are using.
INSERT statements that are based on a SELECT or are using a multi-row syntax to insert more than one row at a time) Oracle, PostgreSQL and DB2 support both, row level and statement level triggers. Microsoft SQL Server only supports statement level triggers and MySQL only supports row level triggers.
124
With SQL 2008: If you are doing 1 update that updates 5 rows, the trigger should be executed only once.
That's why you have to use the tables "INSERTED" and "DELETED" to be able to detect all the modified rows.
If you are doing 5 updates that update 1 row, the trigger will be executed 5 times.
38
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
