G-7740

🆓

Warning

Never handle multiple DML events per trigger if primary key is assigned in trigger.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

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.

Examples

Non-Compliant Example

create or replace trigger dept_br_iu
before insert or update
on departments for each row
begin
   if inserting then
      :new.department_id := department_seq.nextval;
      :new.created_date  := sysdate;
   end if;
   if updating then
      :new.changed_date := sysdate;
   end if;
end;
/

Issues

Line	Column	Message
6	6	Primary key assigned in trigger dept_br_iu with multiple DML events.

Compliant Solution - ★★★★☆

create or replace trigger dept_br_i
before insert
on departments for each row
begin
   :new.department_id := department_seq.nextval;
   :new.created_date  := sysdate;
end;
/

create or replace trigger dept_br_u
before update
on departments for each row
begin
   :new.changed_date := sysdate;
end;
/

Compliant Solution - ★★★★★

alter table department modify department_id default on null department_seq.nextval;
alter table department modify created_date  default on null sysdate;

create or replace trigger dept_br_u
before update
on departments for each row
begin
   :new.changed_date := sysdate;
end;
/

References

same as Trivadis G-7740