Hi, I am learning triggers using Oracle 12c HR sample schema and Oracle Develop PL/SQL Program Units student guide. Oracle says that the trigger execution model is:
1) Execute all BEFORE STATEMENT triggers.
2) Loop for each row affected by the SQL statement:
3) Execute all BEFORE ROW triggers for that row.
4) Execute the DML statement AND PERFORM INTEGRITY CONSTRAINT CHECKING FOR THAT ROW.
5) Execute all AFTER ROW triggers for that row.
6) Execute all AFTER STATEMENT triggers.
The sample HR schema has an EMPLOYEES table with a DEPARTMENT_ID FK recerencing a DEPARTMENT_ID PK in a DEPARTMENTS table.
Correctly, if I try to insert a record in EMPLOYEES having a FK value that does not exist among the PKs in DEPARTMENTS, I will get a ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found error.
As a proof of concept, I create the following trigger to insert the missing record in DEPARTMENTS and avoid the integrity constraint violation:
CREATE OR REPLACE TRIGGER employee_dept_fk_trg
BEFORE UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
INSERT INTO departments (department_id, department_name) VALUES(:new.department_id,'Dept '||:new.department_id);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL; -- mask exception if department already exists
END;
It works fine (I can insert a non existent FK as the corresponding PK would be created first).
The studying material says then that we could make the HR.EMP_DEPT_FK constraint DEFERRABLE INITIALLY DEFERRED in order to postpone the integrity check at the moment of the final COMMIT and not at the end of each INSERT statement (see item #4 in list), and so create an AFTER UPDATE trigger rather than a BEFORE UPDATE one like the previous.
(Are there any advantages of doing so versus just using a BEFORE UPDATE trigger?)
Before altering the HR.EMP_DEPT_FK constraint setting it DEFERRABLE INITIALLY DEFERRED, I wanted to verify the assertion in item #4, so I created an identical AFTER UPDATE trigger and I tried to insert a non existent FK in the EMPLOYEES table: I RECEIVED NO ORA-02291 ERROR and the new PK record in DEPARTMENTS table has been created! It seems that the AFTER UPDATE trigger executes before any integrity constraint check, while in item #4 Oracle says the opposite!
Could you help me on this topic, please?