G-7740: Never handle multiple DML events per trigger if primary key is assigned in trigger.
Critical
Efficiency, Reliability
Unsupported in db* CODECOP Validators
We cannot identify what the primary key column(s) are to check if assignment to a primary key is taking place in the trigger.
Reason
If a trigger makes assignment to the primary key anywhere in the trigger code, that causes the session firing the trigger to take a lock on any child tables with a foreign key to this primary key. Even if the assignment is in for example an if inserting
block and the trigger is fired by an update
statement, such locks still happen unnecessarily. The issue is avoided by having one trigger for the insert containing the primary key assignment, and another trigger for the update. Or even better by handling the insert assignment as ´default on null´ clauses, so that only an on update
trigger is needed.
This locking of child tables behaviour goes for simple DML triggers as well as compound DML triggers where assignments to primary keys take place. It is not relevant for instead-of triggers on views, as it is not possible to assign :new
values and therefore no locks on child tables are needed.
Example (bad)
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
Example (good)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
|
Example (best)
1 2 3 4 5 6 7 8 9 10 |
|