ORACLE Triggers

What are Triggers and types of Triggers in ORACLE?

• Triggers are Reusable PL/SQL programs.
• It cannot be called on its own- they are associated with Tables.
• Triggers are fired implicitly on the tables/views on which they are created.
• When DB event (DML/DDL statements) occurs associated trigger will be invoked to perform actions.
• No select statement will fire trigger.
• Any number of triggers can be created on a single table.
• You can create a maximum of 12 combinations on a table

           3*2*2=12

           Insert/Delete/Update - 3

           Before/After - 2

           Row/Statement- 2  

      Advantages-

• Automatic Backup of data for Update/Delete - Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
• Help to trace unwanted Deletions/Updations
• Implementation of Complex Business rules which are not possible by constraints.
• Help to maintain Data Integrity- Triggers can be used to enforce constraints. For Example: Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
• Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.

Based on the way it executes statements triggers are of two types – (Triggering Levels)

1) Row level trigger - An event is triggered for each row updated, inserted or deleted. 

2) Statement level trigger - An event is triggered for each SQL statement executed. 

Which are further categorized as-

• DDL Triggers
• DML Triggers
• Instead of trigger
• System Events
• Compound Triggers (11g feature)

A Trigger fires for three actions – (Triggering Events)
1. Insert
2. Delete
3. Update

And the trigger can the fired- (Trigger Timing)
1. Before action 
2. After action

Syntax for Creating a Trigger  CREATE [OR REPLACE] TRIGGER <Trigger_Name>  {BEFORE | AFTER | INSTEAD OF}  {INSERT [OR] | UPDATE [OR] | DELETE}  [OF col_name]  ON table_name  [REFERENCING OLD AS o NEW AS n]  [FOR EACH ROW]  WHEN (condition)   BEGIN    --SQL Statements   END;

• CREATE [OR REPLACE] TRIGGERTrigger_Name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
• {BEFORE | AFTER | INSTEAD OF} - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
• [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
• CREATE [OR REPLACE] - TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
• [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
• [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
• [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected (i.e. a Row Level Trigger) or just once when the entire sql statement is executed (i.e. statement level Trigger).
• WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.

We can create a trigger to update the 'Product_Price_History' table when the price of the product is updated in the 'Product' table.

1) Create the 'Product' table and 'Product_Price_History' table

CREATE TABLE Prouct_Price_History 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) ); 
 
CREATE TABLE Product 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) ); 

SQL> SELECT * From PRODUCT;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1001 Samsung                          Flipkart                              34000
      1002 LG                               Flipkart                              12000
      1003 Apple                            Amazon                                54000
      1004 iPhone7                          Amazon                                54000

SQL> SELECT * From PRODUCT_PRICE_HISTORY;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1003 Apple                            Amazon                                89000

2) Create the Price_History_Trigger and execute it.
CREATE or REPLACE TRIGGER Price_History_Trigger 
BEFORE UPDATE OF unit_price 
ON Product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES 
(:old.product_id, 
 :old.product_name, 
 :old.supplier_name, 
 :old.unit_price); 
END; 
SQL> CREATE or REPLACE TRIGGER Price_History_Trigger
  2  BEFORE UPDATE OF unit_price
  3  ON Product
  4  FOR EACH ROW
  5  BEGIN
  6  INSERT INTO product_price_history
  7  VALUES
  8  (:old.product_id,
  9   :old.product_name,
 10   :old.supplier_name,
 11   :old.unit_price);
 12  END;
 13  /

Trigger created.

3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 45000 WHERE product_id = 1003;

Once the above update query is executed, the trigger fires and updates the 'Product_Price_History' table.

SQL> UPDATE PRODUCT SET UNIT_PRICE=77000 Where PRODUCT_ID=1004;

1 row updated.

SQL> SELECT * From PRODUCT_PRICE_HISTORY;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1003 Apple                            Amazon                                89000
      1004 iPhone7                          Amazon                                54000

SQL> SELECT * From PRODUCT;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1001 Samsung                          Flipkart                              34000
      1002 LG                               Flipkart                              12000
      1003 Apple                            Amazon                                54000
      1004 iPhone7                          Amazon                                77000

4) If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * From PRODUCT;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1001 Samsung                          Flipkart                              34000
      1002 LG                               Flipkart                              12000
      1003 Apple                            Amazon                                54000
      1004 iPhone7                          Amazon                                54000

SQL> SELECT * From PRODUCT_PRICE_HISTORY;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1003 Apple                            Amazon                                89000

PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE Row level trigger fires, once for each row affected. 
3) Then AFTER Row level trigger fires once for each affected row.This events will alternate between BEFORE and AFTER row level triggers.

4) Finally the AFTER statement level trigger fires.

For Example: Let's create a table 'Product_Check' which we can use to store messages when triggers are fired.

CREATE TABLE Product_Check (Message varchar2(50), Current_Date DATE);

Let's create a BEFORE and AFTER statement and Row level triggers for the Product table.

1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'Product_Check' before a sql update statement 

is executed, at the statement level.
CREATE or REPLACE TRIGGER 
Before_Update_Stat_product 
BEFORE 
UPDATE ON Product 
Begin 
INSERT INTO Product_check Values ('Before update, statement level’, Sysdate); 
END; 

2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'Product_Check' before each row is updated.

CREATE or REPLACE TRIGGER Before_Upddate_Row_product
 BEFORE
 UPDATE ON Product 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO Product_Check 
 Values ('Before update row level’, Sysdate); 
 END; 



3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table 'Product_Check' after a sql update statement 


is executed, at the statement level.
CREATE or REPLACE TRIGGER
 After_Update_Stat_product 
 AFTER 
 UPDATE ON Product 
 BEGIN 
 INSERT INTO Product_Check 
 Values ('After update, statement level', Sysdate); 
 End; 

4) AFTER UPDATE, Row Level: This trigger will insert a record into the table 'Product_Check' after each row is updated.
 CREATE or REPLACE TRIGGER
 After_Update_Row_product 
 AFTER  
 UPDATE On Product 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO product_check 
 Values ('After update, Row level', Sysdate); 
 END; 

SQL> DESC PRODUCT_CHECK;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 MESSAGE                          VARCHAR2(50)
 CURRENT_DATE                     DATE

SQL> CREATE or REPLACE
  2  TRIGGER Before_Update_Stat_product
  3  BEFORE
  4  UPDATE ON Product
  5  Begin
  6  INSERT INTO Product_check Values ('Before update, statement level’, Sysdate);
  7  END;
  8  /

Trigger created.

SQL>  CREATE or REPLACE TRIGGER Before_Upddate_Row_product
  2   BEFORE
  3   UPDATE ON Product
  4   FOR EACH ROW
  5   BEGIN
  6   INSERT INTO Product_Check
  7   Values ('Before update row level’, Sysdate);
  8   END;
  9  /

Trigger created.

SQL>  CREATE or REPLACE TRIGGER
  2   After_Update_Stat_product
  3   AFTER
  4   UPDATE ON Product
  5   BEGIN
  6   INSERT INTO Product_Check
  7   Values ('After update, statement level', Sysdate);
  8   End;
  9  /

Trigger created.

SQL> CREATE or REPLACE TRIGGER
  2   After_Update_Row_product
  3   AFTER
  4   UPDATE On Product
  5   FOR EACH ROW
  6   BEGIN
  7   INSERT INTO product_check
  8   Values ('After update, Row level', Sysdate);
  9   END;
 10  /

Trigger created.

Now let’s execute an update statement on table product.

UPDATE PRODUCT SET Unit_Price = 54000 WHERE Product_ID in (1003,1004); 

Let’s check the data in 'Product_Check' table to see the order in which the trigger is fired.

SELECT * FROM Product_Check; 
SQL> SELECT * From PRODUCT;

PRODUCT_ID PRODUCT_NAME                     SUPPLIER_NAME                    UNIT_PRICE
---------- -------------------------------- -------------------------------- ----------
      1001 Samsung                          Flipkart                              34000
      1002 LG                               Flipkart                              12000
      1003 Apple                            Amazon                                54000
      1004 iPhone7                          Amazon                                54000

SQL> UPDATE PRODUCT SET Unit_Price = 84000 Where Product_ID in (1002,1003);

2 rows updated.

SQL> SELECT * FROM PRODUCT_CHECK;

MESSAGE                                            CURRENT_D
-------------------------------------------------- ---------
Before update, statement level                     02-NOV-16
Before update row level                            02-NOV-16
After update, Row level                            02-NOV-16
Before update row level                            02-NOV-16
After update, Row level                            02-NOV-16
After update, statement level                      02-NOV-16

12 rows selected.

The above result shows 'Before Update' and 'After Update' Row Level Events have occured twice, since two records were

updated. But 'Before Update' and 'After Update' statement level Events are fired only once per SQL statement.

Instead of Triggers on Views Example in Oracle

Instead of triggers in oracle database are defined on views only. 

A normal DML trigger executes only when a DML operation is issued on a table.Whereas Instead of trigger fires when a DML statment is issued on the view. 

Instead-of triggers must be row level. 
CREATE INSTEAD OF TRIGGER:

Take a look at the following view definition: 

Create or Replace View EMP_DEPT_INFO As 
Select D.DEPTNO, D.DEPARTMENT, E.NAME
From EMP_TEST E, DEPT_TEST D
Where E.DEPTNO = D.DEPTNO;

As the view consists of two table joins, it is illegal to insert records into this view as the insert requires both the underlying tables 

to be modified. 

By creating an instead-of trigger on the view, you can insert the records into both the
underlying tables.  Let’s try this now- 


CREATE SEQUENCE EMP_SEQUENCE
  MINVALUE 101
  MAXVALUE 1000
  START WITH 101
  INCREMENT BY 1
  CACHE 20;

CREATE SEQUENCE DEPT_SEQUENCE
  MINVALUE 10
  MAXVALUE 500
  START WITH 10
  INCREMENT BY 10
  CACHE 20;

CREATE OR REPLACE TRIGGER UpdteEmpDept
INSTEAD OF INSERT ON EMP_DEPT_INFO
DECLARE
V_ID
DEPT_TEST.DEPTNO%TYPE;
BEGIN
 BEGIN
SELECT DEPTNO INTO V_ID
 FROM   DEPT_TEST WHERE DEPTNO=
:new.DEPTNO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO DEPT_TEST VALUES (Dept_Sequence.NextVal, :new.Department)
RETURNING DEPTNO INTO V_ID;
END;
INSERT INTO EMP_TEST (ID, NAME, DEPTNO) VALUES (Emp_Sequence.NextVal, :new.NAME, V_ID);
END UpdteEmpDept;

SQL> CREATE OR REPLACE TRIGGER UpdteEmpDept
  2  INSTEAD OF INSERT ON EMP_DEPT_INFO
  3  DECLARE
  4  V_ID
  5  DEPT_TEST.DEPTNO%TYPE;
  6  BEGIN
  7   BEGIN
  8  SELECT DEPTNO INTO V_ID
  9   FROM   DEPT_TEST WHERE DEPTNO=
 10  :new.DEPTNO;
 11  EXCEPTION
 12  WHEN NO_DATA_FOUND THEN
 13  INSERT INTO DEPT_TEST VALUES (Dept_Sequence.NextVal, :new.Department)
 14  RETURNING DEPTNO INTO V_ID;
 15  END;
 16  INSERT INTO EMP_TEST (ID, NAME, DEPTNO) VALUES (Emp_Sequence.NextVal, :new.NAME, V_ID);
 17  END UpdteEmpDept;
 18  /

Trigger created.

Now Insert record into View.

SQL> INSERT INTO EMP_DEPT_INFO VALUES(70,'Communication','Akhil');

1 row created.

SQL> SELECT * FROM EMP_DEPT_INFO;

    DEPTNO DEPARTMENT           NAME
---------- -------------------- ----------
        70 Communication        Akhil
        20 Insurance            Ankit
        10 ENU                  Nikhil
        20 Insurance            Rajan
        20 Insurance            Karan
        10 ENU                  Sajal
        30 Security             Ravi
        30 Security             Surya
        20 Insurance            Sam
        10 ENU                  Jack

10 rows selected.

Now check both the Tables

SQL> SELECT * FROM DEPT_TEST;

    DEPTNO DEPARTMENT
---------- --------------------
        30 Security
        10 ENU
        20 Insurance
        40 Retail
        50 BFSI
        60 CISCO
        70 Communication

7 rows selected.

SQL> SELECT * FROM EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       115 Akhil                                    70
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

10 rows selected.

Trigger has inserted Data into both the Tables. 

I will be updating this Post or will publish new blog for DDL, System and Event Triggers.

It's RUDE to Read and Run! 

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.