G-7730

🆓

Warning

Avoid multiple DML events per trigger.

Rule Details

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

Reason

Rather than a single trigger handling multiple DML events with separated blocks of if inserting, if updating and if deleting, modularity by individual triggers per DML event helps maintaining and testing the code. If most of the code is common for either DML event (only small pieces of code are individual) consider an exception to the rule and allow if inserting, if updating and if deleting blocks, or alternatively gather the common code in a procedure and let individual triggers handle the individual pieces of code plus call the procedure with the common code.

If the trigger makes assignment to a primary key and there are child tables with a foreign key referring to this primary key, the database can make undesirable table locks. If such is the case, you should always use individual triggers. See G-7740 for details.

Example

Non-Compliant Example

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

Issues

Line	Column	Message
2	8	Multiple DML events on trigger debt_br_iu.

Compliant Solution - ★★★★★

create or replace trigger dept_br_i
before insert
on departments for each row
begin
   :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;
/

References

same as Trivadis G-7730