Compound Triggers in Oracle

Compound Triggers in Oracle with Examples

In this article, I am going to discuss Compound Triggers in Oracle with Examples. Please read our previous article where we discussed Trigger Execution Order in Oracle using Follows Clause with Examples.

Compound Triggers in Oracle

The Compound Triggers in Oracle are the most important in representing the triggers that lead to the execution and function of a trigger.

Compound Triggers in Oracle with Examples

Compound Triggers in Oracle:

Let us assume that we have created a table and created four triggers for the table. We have created before statement trigger, before each row trigger, after each row trigger, and after statement trigger. So, oracle combines all these individual triggers into one trigger. The trigger is mentioned as the compound trigger. We can compound all these 4 triggers into one trigger. We will have each section for each trigger.

Note: We should follow the same order. If we change the order then we may face errors while compiling the trigger. For example, we only need two triggers before statement and after statement trigger. Then also, the procedure is the same, first, we need to include the before statement and then the after statement.

What is Compound Trigger in Oracle?

A single trigger on a table that allows you to specify actions for each of the following four timing points:

  • Before the firing statement
  • Before each row that the firing statement affects
  • After each row that the firing statement affects
  • After the firing statement.

The compound trigger started from the oracle 11g. Let us go ahead and see the example using the compound trigger and understand more about it.

Examples to Understand Oracle Compound Triggers:
Step 1: Create a table

In order to test the compound triggers, let us create a table emp_test by executing the below Create Table Statement.

Create table emp_test
(
emp_id number,
First_name varchar2(100)
);

What is Compound Trigger in Oracle?

So, the table is created. Let us go ahead and create a compound trigger.

Step 2: Creation of Compound Trigger in Oracle.

As we discussed the compound trigger consists of four triggers. We are going to include all these triggers in the compound trigger. Below is the syntax of the compound trigger.

create or replace trigger comp_test
for insert or update or delete
on emp_test
compound trigger
--we can define any variables here..
      before statement is
      begin
      DBMS_OUTPUT.PUT_LINE('1');
      end  before statement;
      
      before each row is
      begin
      DBMS_OUTPUT.PUT_LINE('2');
      end before each row;
      
      after each row is
      begin
      DBMS_OUTPUT.PUT_LINE('3');
      end after each row;
      
      after statement is
      begin
      DBMS_OUTPUT.PUT_LINE('4');
      end after statement;
end;

The code is very simple. We have created the compound trigger with the name comp_test for the event insert or update or delete on the table emp_test. We have mentioned the compound trigger in the SQL Statement which indicates the starting of the compound trigger as follows.

create or replace trigger comp_test
for insert or update or delete
on emp_test
compound trigger

If we need to declare any variables we can define them after the compound trigger. Then after this syntax, we will be defining each individual trigger. The syntax is given below.

      before statement is
      begin
      DBMS_OUTPUT.PUT_LINE('1');
      end  before statement;
      
      before each row is
      begin
      DBMS_OUTPUT.PUT_LINE('2');
      end before each row;
      
      after each row is
      begin
      DBMS_OUTPUT.PUT_LINE('3');
      end after each row;
      
      after statement is
      begin
      DBMS_OUTPUT.PUT_LINE('4');
      end after statement;
end;

We have mentioned before statement is and the code fore for before statement trigger and end before statement trigger. The second trigger is before each row trigger and after each row and finally after statement trigger. These are the four triggers and we have mentioned the code of each trigger individually.

So, we have four trigger codes. If the before statement trigger is executed then DBMS output will display “1”. If the before each row trigger is executed the DBMS output will display “2”. If after each row trigger is executed then DBMS output will display “3”. Similarly, after statement will display DBMS output as “4”. Let us go ahead and compile the trigger.

Examples to Understand Oracle Compound Triggers

So, the trigger comp_test is compiled successfully.

Step 3: Test the Compound Trigger in Oracle

Let us go ahead and test the compound trigger by inserting a record into the table emp_test by executing the below insert statement.

Insert into emp_test Values(1,’abc’);

Test the Compound Trigger in Oracle

So, the record is inserted. Let us go ahead and check the DBMS output and see if the number 1,2,3,4 are printed or not.

DBMS Output:

Compound Triggers in Oracle with Examples

From the DBMS output we can see 1,2,3,4 is displayed. So, all four triggers are executed successfully. Let us try to do something more interesting. Let us try to insert two records at a time and see how these triggers react and display the DBMS output by executing the below SQL Statement.

Insert into emp_test
Select employee_id, first_name
From employees
Where department_id=20;

Compound Triggers in Oracle with Examples

So, two records are inserted. Let us try to see the DBMS output and see how the output is generated.

Compound Triggers in Oracle with Examples

So, we can see numbers 2 and 3 were printed twice because these are the triggers for each row. Before each row trigger is executed twice and after each row trigger is executed twice. The other two triggers were executed only once. So, this is the meaning of compound triggers. These are the four individual triggers present in the compound trigger.

In the next article, I am going to discuss Mutating Tables in Oracle with Examples. Here, in this article, I try to explain Compound Triggers in Oracle with Examples. I hope you enjoy this Compound Triggers in Oracle with Examples article.

Leave a Reply

Your email address will not be published. Required fields are marked *