What is the order of tirriger & constraints firing

[kllick123]

Hi,

If I had a table on which I had applied the column level contraints as primary key, foriegin key or check level constaints.

Also I had applied the triggers on it(Both statemenmt & row level).

Then what will fire first a constaint or a trigger ?

 

[markrem]

If you want to modify the column data on the fly, prior to being posted to the database, then you want to use a BEFORE trigger FOR EACH ROW. BEFORE triggers are fired before constraints. For example, they can be used to populate a PK column before the constraint has a chance to determine if the value is null or not.

If you try to use an AFTER trigger, then Oracle will tells you two things:
1. If you try to change the value of :new.column, Oracle will tell you "ORA-04084: cannot change NEW values for this trigger type."
2. If you try to change the value of ld.column, Oracle will tell you "ORA-04085: cannot change the value of an OLD reference variable."

I don't see the point in trying to do dynamic key setting on anything other than a row level trigger.

-Mark

9i OCP