oracle11g - 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

If you need to be certain about the order of trigger execution, you can specify this order when creating the trigger.

This is done with the FOLLOWS ... and PRECEEDS ... options of the create trigger statement:

More details in the manual: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#CJAEJAFB

FOLLOWS | PRECEDES

Specifies the relative firing of triggers that have the same timing point. It is especially useful when creating crossedition triggers, which must fire in a specific order to achieve their purpose.

Use FOLLOWS to indicate that the trigger being created must fire after the specified triggers. You can specify FOLLOWS for a conventional trigger or for a forward crossedition trigger.

Use PRECEDES to indicate that the trigger being created must fire before the specified triggers. You can specify PRECEDES only for a reverse crossedition trigger.