After Trigger execute before constraint check in oracle

Question

Hi, I have After Insert/Update trigger on Table T1 which get the referential data for Col1 from T2 and does some work and insert it into another table. The col1 is FK to Table T2.

When user insert the incorrect or non existing value into the Col1 and if trigger is disabled I am getting constraint error that is fine.

But when trigger is enabled and user insert the wrong value in Col1 trigger is getting fired and shows the 'no data found' error message.

Actually I am expecting the table to throw constraint error, but trigger is throwing it.

Please let me know your comments about this trigger behaviour.

Answer 1

I'm guessing the trigger would have to be a BEFORE trigger. It will run prior to the constraints being checked. If the trigger raises NO_DATA_FOUND, then the constraint never gets checked. If the trigger is disabled, it is not run, so the constraint gets checked.

Answer 2

You do not mention whether you are using BEFORE or AFTER triggers. Please check the documentation for the order of execution:

• BEFORE statement
• BEFORE row
• CONSTRAINTS
• AFTER statement
• AFTER row