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)

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)

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