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