Skip to content

G-7710: Avoid cascading triggers.

Major

Maintainability, Testability

Reason

Having triggers that act on other tables in a way that causes triggers on that table to fire lead to obscure behavior.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
create or replace trigger dept_br_u
before update on departments for each row
begin
   insert into departments_hist (
      department_id
     ,department_name
     ,manager_id
     ,location_id
     ,modification_date)
   values (
      :old.department_id
     ,:old.department_name
     ,:old.manager_id
     ,:old.location_id
     ,sysdate);
end;
/
create or replace trigger dept_hist_br_i
before insert on departments_hist for each row
begin
   insert into departments_log (
      department_id
     ,department_name
     ,modification_date)
   values (
      :new.department_id
     ,:new.department_name
     ,sysdate);
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create or replace trigger dept_br_u
before update on departments for each row
begin
   insert into departments_hist (
      department_id
     ,department_name
     ,manager_id
     ,location_id
     ,modification_date)
   values (
      :old.department_id
     ,:old.department_name
     ,:old.manager_id
     ,:old.location_id
     ,sysdate);

   insert into departments_log (
      department_id
     ,department_name
     ,modification_date)
   values (
      :old.department_id
     ,:old.department_name
     ,sysdate);

end;
/