FOLLOWS Clause in Oracle Triggers - Setting Trigger Precedence

Follows Clause in Triggers - Precedence of Triggers

Triggers are Named sub-programs, which are used to perform some actions based on the triggering event, perform auditing and also to implement business rules.   

As we are aware that we can have multiple DML triggers on a table. They can be of different types like row level and Statement level triggers. We can have multiple triggers at different triggering timings and triggering Events.

 We can even have multiple triggers in the same triggering event and triggering timing. That is, we can have multiple BEFORE INSERT ROW LEVEL or AFTER INSERT ROW LEVEL or BEFORE UPDATE INSERT FOR EACH ROW, etc.

Triggers which are in the same event and timing are fired automatically when the corresponding triggering event happen on the associated table. But, there is no guarantee of the order of execution of the triggers in the versions till Oracle 10. Oracle has introduced FOLLOWS clause to control the flow of execution of these triggers which are in the same type.

Here is a sample to implement FOLLOWS clause.  

I have created a table CUSTOMER.

CREATE TABLE CUSTOMER

(
CUST_ID NUMBER(4),
CUST_NAME VARCHAR2(20),
DOB DATE,
AGE NUMBER,
AGE_BUCKET VARCHAR2(20),
INSERTED_DT TIMESTAMP(0)
);

I have created 2 triggers on this table in with same type, triggering event and timing. Both of them are BEFORE INSERT and FOR EACH ROW LEVEL.

They are –

1.       TRG_BRI_Customer_1 à Calculating the age based on DOB before record is getting inserted.

CREATE or REPLACE TRIGGER TRG_BRI_CUSTOMER_1

BEFORE INSERT on CUSTOMER
FOR EACH ROW
BEGIN
:NEW.age := ceil(months_between(sysdate,:new.dob)/12);
:NEW.Inserted_Dt := sysdate;
Dbms_output.put_line('Trigger TRG_BRI_ CUSTOMER_1  is fired');
End;
/

2.       TRG_BRI_Customer_2 :  

-          Categorizes the customer based on the age which is being calculated by TRG_BRI_Customer_1.

-          I have created a function IS_NUM to check whether a value is a not null value or not. This trigger used this function to check whether the calculated value is a valid value or no. If the value is a not null value and is a number, it will assign respective age bucket to the record. Else we see null.   

CREATE or REPLACE TRIGGER TRG_BRI_CUSTOMER_2

BEFORE INSERT on CUSTOMER
FOR EACH ROW
DECLARE
v_age varchar2(20);
BEGIN
v_age := is_num(:NEW.Age);
IF v_age = 0 then
:NEW.Age_bucket :=
Case WHEN :NEW.Age > 65 then 'Senior Citizen'
WHEN :NEW.Age  between 41 and 65 THEN 'Adult'
WHEN :NEW.Age between 18 and 40 THEN 'young Adult'
WHEN :NEW.Age between 13 and 18 THEN 'Teens'  
ELSE 'Childhood' END;
ELSE
Dbms_output.put_line(' Age value is Not a number');
END IF;
Dbms_output.put_line('Trigger TRG_BRI_ CUSTOMER_2 is  fired');
End;

Let us insert a record in the table.

SQL> insert into customer (cust_id,cust_name,dob) values (1,'Raghu','01-may-99');

Age value is Not a number
Trigger TRG_BRI_ CUSTOMER_2 is  fired
Trigger TRG_BRI_ CUSTOMER_1  is fired
1 row created.

SQL> select * from customer;

   CUST_ID CUST_NAME                DOB                       AGE       AGE_BUCKET           INSERTED_DT
   ----------  -----------------              ---------                                 ---------- -------------------- --------------------------
        1          Raghu               01-MAY-99         18                                     12-DEC-16 04.58.10 PM

You can make out the AGE_BUCKET is null in the table. This is because By the time TRG_BRI_ CUSTOMER_2 is fired, TRG_BRI_ CUSTOMER_1 is still calculating Age of the customer.

The solution here is to define the execution order in the second trigger to fire only after the first one. We have to give FOLLOWS clause in the second trigger as follows -  

CREATE or REPLACE TRIGGER TRG_BRI_CUSTOMER_2

BEFORE INSERT on CUSTOMER
FOR EACH ROW
FOLLOWS TRG_BRI_CUSTOMER_1
DECLARE
v_age varchar2(20);
BEGIN
v_age := is_num(:NEW.Age);
IF v_age = 0 then
:NEW.Age_bucket :=
Case WHEN :NEW.Age > 65 then 'Senior Citizen'
WHEN :NEW.Age  between 41 and 65 THEN 'Adult'
WHEN :NEW.Age between 18 and 40 THEN 'young Adult'
WHEN :NEW.Age between 13 and 18 THEN 'Teens'  
ELSE 'Childhood' END;
ELSE
Dbms_output.put_line(' Age value is Not a number');
END IF;
Dbms_output.put_line('Trigger TRG_BRI_ CUSTOMER_2 is  fired');
End;

Here is the output after inserting another record after adding follows clause in trigger.

SQL> insert into customer (cust_id,cust_name,dob) values (2,'Raghuram','01-may-80');
Trigger TRG_BRI_ CUSTOMER _1  is fired
Trigger TRG_BRI_ CUSTOMER_2 is  fired
1 row created.
SQL> select * from customer;

   CUST_ID   CUST_NAME     DOB              AGE       AGE_BUCKET           INSERTED_DT

    ----------     ----------------- ---------            ---------- -------------------- -------------------------------
       1             Raghu             01-MAY-99         18                                  12-DEC-16 05.34.07 PM

       2             Raghuram      01-MAY-80         37     young Adult       12-DEC-16 05.35.24 PM

Because of the execution order, the second trigger is fired after the first one. Now we can see Both Age and AGE_BUCKET are populated.