Order of execution of trigger and statements in Oracle stored procedure

Question

Below are my table structures :

Table -Customer
CustomerID Blacklisted Customer Name
101 Y ABC
102 Y DEF

Table -Blacklist
CustomerID BlacklistID Customer Name
101 1011 ABC
102 1012 DEF

Table -Reason
BlacklistID ReasonID Reason Code
1012 02 Rcode2

Main table "Customer" is to store customer information.There is a trigger after update on table "Customer" to insert record in table "Blacklist" if somebody updates the blacklisted as Y in customer table. We consider the customer as blacklisted if ,

• Blacklisted column in Customer table as value 'Y' and.
• There are records present for customer in Blacklist and Reason table

Now my requirement is to blacklist the customer from backend.For this i am writing stored procedure with below queries:

1. Update customer set blacklisted ='Y' where customerid='102';
2. select BlacklistID into var_id from blacklist where customerid='102';
3. Insert into reason(BlacklistID,ReasonID,ReasonCode)values(var_ id,111,'RCODE1');

Now to insert entry in Reason table(step-3),i need BlacklistID which is a foreign key and i will get the value of BlacklistID once the trigger on customer table gets exceuted.So my confusion is, can i assume the trigger on update of 'Customer' table will always get excuted before the cntrl reaches my INSERT INTO reason(step-3) statement. Please suggest.

Answer 1

Yes. Triggers are part of the statement. Although you cannot be fully certain *) of the order in which multiple triggers in the same statement are executed, you can be certain that they al are done when the statement itself is done. So by the time of step 2, all update triggers of step one have fired.

*) Actually, the default order is:

1. Statement level before triggers
2. Row level before triggers
3. Row level after triggers
4. Statement level after triggers

But if you have, say, two row level before trigger, by default you cannot be certain in which order those two are executed. But I learned from the comments that in Oracle 11, you can actually specify the order to cover even those cases.