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.

Note that the example below is an anti-pattern as Flashback Data Archive should be used for row history instead of history tables.

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
create or replace trigger dept_br_u
before update on department for each row
begin
   insert into department_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 department_hist for each row
begin
   insert into department_log (department_id
                               ,department_name
                               ,modification_date) 
                        values (:new.department_id
                               ,:new.department_name
                               ,sysdate);
end;
/

Example (good)

Note: Again, don't use triggers to maintain history, use Flashback Data Archive instead.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create or replace trigger dept_br_u
before update on department for each row
begin
   insert into department_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 department_log (department_id
                               ,department_name
                               ,modification_date) 
                        values (:old.department_id
                               ,:old.department_name
                               ,sysdate);

end;
/