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.

Triggers

Trigger is a Named Sub-Program which gets fired whenever an action occur in the table. It can be DML’s on the associated table or view, DDL issued in a schema or database or an action in the database like startup, shutdown, logon, logoff, etc. Triggering Events in case of DML’s can be an INSERT, UPDATE or DELETE. Triggers are fired no matter which user is connected or application being used. Database implicitly executes these triggers whenever the action event occurs. User cannot execute them explicitly.

Based on the number of times the trigger action to be executed user can define the trigger. There are 2 types of DML triggers. They are –

             1.       Statement level Trigger and  

             2.       Row level Triggers.

Statement Level triggers are fired once regardless of the number of rows the triggering statement affects the associated table.

Row Level triggers are fired for every record the DML is affecting the associated table. In other words, they are fired for every row affected by the triggering statement.

Basic difference between statement level and row level trigger is -

Statement level triggers are fired regardless of the number of rows the triggering statement is affecting. They are fired once even if there are no affected rows. But, Row level triggers are fired only when the rows in the associated table are affected.  

We can specify the trigger timing when the triggering action to be executed either BEFORE or AFTER the triggering statement. We can use BEFORE or AFTER in both the statement level Triggers and Row level Triggers.  

We can create four types of triggers with the combination of the type of trigger and the timing. They are -

Before Statement Level – These are fired Before the triggering statement is fired.

After Statement Level – These are fired AFTER the triggering Statement is fired.

Before Row Level – Before the triggering statement is affecting each row in the associated table. 

After Row Level - Before the triggering statement is affecting each row in the associated table.

Trigger execution Sequence –

1. All Before Statement Level Triggers.
2. All Before Row Level Triggers.
3. All After Row Level Triggers.
4. All After Statement Level Triggers.

We can have multiple DML triggers on a table.

The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction.

There are 3 basic parts in the trigger. 

1.       Triggering Statement or Event

2.       Trigger Restriction

3.       Trigger Action

Syntax for Creating Trigger –

CREATE [OR REPLACE ] TRIGGER trigger_name

 {BEFORE | AFTER | INSTEAD OF }                   à Triggering Event Timing

 {INSERT [OR] | UPDATE [OR] | DELETE}  à Triggering Event

 [OF col_name]

 ON table_name

 [REFERENCING OLD AS o NEW AS n]

 [FOR EACH ROW]

 WHEN (condition)                         à Trigger Restriction

 BEGIN

   --- sql statements                 à Trigger Action

 END; 

CREATE [OR REPLACE ] TRIGGER trigger_name  à Creates trigger with the given name if it is not there or Overwrites the existing one.

{BEFORE | AFTER | INSTEAD OF } à This clause indicates the triggering time to fire.

{INSERT [OR] | UPDATE [OR] | DELETE} à indicates the triggering Event.

REFERENCING à This clause is used to reference Old and New Values being changed. We refer the old and new values as - :NEW.Column_Name and :OLD.Column_Name respectively.

[FOR EACH ROW] à Type of the trigger. If this is not specified, it will be treated as a STATEMENT LEVEL Trigger

WHEN à This clause is used to check the condition for rows. Triggers are fired only when the row satisfies the condition. In otherwords, trigger is fired only when the condition evaluates to TRUE. Trigger event won’t be executed If it is FALSE or UNKNOWN. This Clause is valid for only row level triggers.  

Restrictions on Trigger Conditions Trigger conditions are subject to the following restrictions:

·         If you specify this clause for a DML event trigger, then you must also specify FOR EACH ROW. Oracle Database evaluates this condition for each row affected by the triggering statement.

·         You cannot specify trigger conditions for INSTEAD OF trigger statements.

You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger condition.

Data Dictionary view to view triggers is – USER_TRIGGERS. We can even query USER_SOURCES for getting information about the triggers.

 The structure of USER_TRIGGERS is –

Name	Type
TRIGGER_NAME	VARCHAR2(128)
TRIGGER_TYPE	VARCHAR2(16)
TRIGGERING_EVENT	VARCHAR2(246)
TABLE_OWNER	VARCHAR2(128)
BASE_OBJECT_TYPE	VARCHAR2(18)
TABLE_NAME	VARCHAR2(128)
COLUMN_NAME	VARCHAR2(4000)
REFERENCING_NAMES	VARCHAR2(422)
WHEN_CLAUSE	VARCHAR2(4000)
STATUS	VARCHAR2(8)
DESCRIPTION	VARCHAR2(4000)
ACTION_TYPE	VARCHAR2(11)
TRIGGER_BODY	LONG
CROSSEDITION	VARCHAR2(7)
BEFORE_STATEMENT	VARCHAR2(3)
BEFORE_ROW	VARCHAR2(3)
AFTER_ROW	VARCHAR2(3)
AFTER_STATEMENT	VARCHAR2(3)
INSTEAD_OF_ROW	VARCHAR2(3)
FIRE_ONCE	VARCHAR2(3)
APPLY_SERVER_ONLY	VARCHAR2(3)