Before update trigger with referential integrity in oracle 11g

Question

I want to understand what does before update in trigger means.

I have a table called DEPT_MST where DEPT_ID is the primary key. It has 2 rows with DEPT_ID 1 and 2.

Another table EMP has columns EMP_ID as primary key and EMP_DEPT_ID which is a foreign key referencing DEPT_ID of DEPT table.

Now if I add before update trigger on EMP tables EMP_DEPT_ID column which will check if new value for EMP_DEPT_ID is present in master table DEPT if now then will insert new row with new DEPT_ID to DEPT table.

Now if I update EMP_DEPT_ID to 3 where EMP_DEPT_ID is 2 in EMP table it is giving integrity constraint violation error parent not found.

So,

1. Does this mean that Oracle checks for integrity constraints first and then calls the "before update" trigger?
2. Then how can we bypass this check and call before update trigger?
3. What exactly does "before update" mean here?
4. How can I achieve above result by using triggers and not by using explicit PL SQL block?

Thank you

Answer 1

Non-deferred foreign key constraints are evaluated before triggers are called, yes.

If you can declare the foreign key constraint to be deferrable (which would require dropping and re-creating it if the existing constraint is not deferrable)

ALTER TABLE emp
  ADD CONSTRAINT fk_emp_dept (emp_dept_id) REFERENCES dept( dept_id )
  INITIALLY DEFERRED DEFERRABLE;

In your application, you can then set the constraint to be deferrable, run your INSERT statement causing the trigger to fire and insert the parent row. Your foreign key constraint will be validated when the transaction commits.

An alternative to defining the constraint to be deferrable would be to rename the emp table to, say, emp_base, create a view named emp and then create an instead of insert trigger on emp that implements the logic of first inserting into dept and then inserting into emp_base.