G-7730: Avoid multiple DML events per trigger.

Minor

Maintainability, Testability

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 (bad)

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;
/

Example (good)

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;
/