CODEWITHSUNDEEP
oracle11g
user2548985
user2548985

Reputation: 13

Order of execution of trigger and statements in Oracle stored procedure

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 ,

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.

Upvotes: 1

Views: 16305

Answers (4)

Bahram
Bahram

Reputation: 1

use this keyword:

FOLLOWS

for example:

trigger1

trigger2

In declare section of trigger2 write:

FOLLOWS trigger1

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

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.

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52376

I don't see a need for all these tables, and therefore no need for a trigger.

Why do you not just use a blacklist reason code in the customer table? The purpose of the blacklist table is unclear as it seems to add no data and just repeats data from the customer table with an additional id column?

Or if you need multiple reason codes then just use a blacklist_reason table that references the customer id and a reason code -- I don't think you even need the blacklisted column in the customer table.

Upvotes: 1

Related Questions