PL/SQL Triggers – Explained in detail

PL/SQL Triggers is one of the most important topic in PL/SQL curricula. As we know Triggers acts like stored procedures but its execution is automatic before or after or when certain event occurs. Please note triggers are always system-defined and never user-defined. Can you enable or disable a procedure? No, but you can enable or disable triggers at your own wish. Let us find in detail about PL/SQL Triggers.


What are Triggers?

You enter into a lift of a building and press a floor button on which you wish to go. As soon as you press that button lift goes up automatically and stops at that floor. Concept of trigger is as simple as this example. Here you enter into your SQL screen (enter into your building), run any DML statement (press a floor button) and execute. You will reach to your results just like the life. Lift going upwards is an automatic process as execution of trigger. It requires no human interaction or manual process to execute a trigger.

Theoretically, a trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs.

Syntax to create PL/SQL Trigger

  [OF col_name]
  ON tbl_name

CREATE [OR REPLACE ] TRIGGER trigger_name – This clause creates a new trigger or replace an existing trigger.

{ BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time the trigger should get fired. i.e before or after inserting, deleting or updating a table. INSTEAD OF is used only to create a trigger on a view.

– This clause determines the DML operation before or after which the trigger should be fired. All the three events can be used together with an OR clause in between.

[OF col_name] – A column on which Trigger should be fired. This can be used only with update triggers when you want to trigger an event when a specific column is updated.

[ON table_name] – Name of the table on which trigger should be fired

[REFERENCING OLD AS o NEW AS n] – This clause reference existing data as OLD and inserted or updated data as NEW. By default, you reference the values as: old.column_name or: new.column_name. You can use different names other than “o” and “n”. One 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.

Drop Trigger

DROP TRIGGER trigger_name;

The above command will drop the trigger permanently.

Alter Trigger

ALTER TRIGGER command can be used to rename, compile, disable and enable the trigger.

Compile Trigger
ALTER TRIGGER trigger_name compile;
Disable Trigger
ALTER TRIGGER trigger_name disable;
Enable Trigger
ALTER TRIGGER trigger_name enable;
Rename Trigger
ALTER TRIGGER old_trigger_name1 rename to new_trigger_name;

Steps to Create Trigger With Example

Every time you raise a salary of an employee, history should be maintained in employee_history table

1) Create table employee

CREATE TABLE employee (emp_id NUMBER, emp_Name VARCHAR2(140), salary NUMBER);

2) Create table employee_history

CREATE TABLE employee_history (emp_id NUMBER, emp_Name VARCHAR2(140), salary NUMBER);

3) Create trigger salary_history_trigger and execute it:

CREATE OR REPLACE TRIGGER salary_history_trigger
  ON employee
  INSERT INTO employee_history VALUES (:old.emp_id, :old.emp_name, :old.salary);

4) Update salary in table employee

UPDATE employee SET salary = 1000 WHERE emp_id = 1;

Once the transaction is committed a row will fall in employee_history table. If the transaction is rolled back the entry in table employee_history will also be rolled back.

Why to use Triggers?

Triggers are used for purposes that are listed below:

  • Gather statistics data on table access
  • Auditing purpose like to find out who inserted, deleted row and modified records
  • Log events
  • Prevent invalid transactions from unauthorized access
  • Restrict DML operations in business hours only
  • Enforce referential integrity when child and parent tables are on different nodes of a distributed database system
  • Enforce complex business rules

Types of Triggers

  • DML Triggers
    DML Triggers are fired on INSERT, UPDATE and DELETE level (Before Insert and After Insert, Before Update and After Update, Before Delete and After Delete)

    1. Row Level Triggers
      When a row is inserted, deleted or updated in a table, row level trigger fires.
    2. Statement Level Triggers
      When a statement is executed, statement level trigger is fired.

Click here to learn more about DML Triggers

  • Instead Of Triggers
    Instead Of triggers in oracle are used for views only.

Click here to learn more about Instead Of Triggers

  • DDL Triggers
    Triggers fired on DDL Level are called DDL Triggers (AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA).

Click here to learn more about DDL Triggers

Information about Triggers

Data dictionary view USER_TRIGGERS contains information about triggers

SELECT * FROM user_triggers WHERE trigger_name = ‘salary_history_trigger’;
Structure of USER_TRIGGERS
NAME                        Type
 ------------------         ---------------
 TRIGGER_NAME               VARCHAR2(30)
 TRIGGER_TYPE               VARCHAR2(16)
 TRIGGER_EVENT              VARCHAR2(75)
 TABLE_OWNER                VARCHAR2(30)
 TABLE_NAME                 VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 WHEN_CLAUSE                VARCHAR2(4000)
 STATUS                     VARCHAR2(8)
 DESCRIPTION                VARCHAR2(4000)
 ACTION_TYPE                VARCHAR2(11)
 TRIGGER_BODY               LONG

CYCLIC CASCADING in a Trigger (Infinite Loop inTrigger)

Cyclic cascading in a trigger occurs when two triggers goes into an infinite loop

Let’s consider we have two table employee and employee_history. These are the two triggers created

  • The INSERT Trigger, ins_emp_trig on table employee issues an UPDATE on table employee_history.
  • The UPDATE Trigger, upd_emp_trig on table employee_history issues an INSERT on table employee.

When a record is inserted into employee table and update is issued on employee_history table, as soon as the update is made on employee_history table, insert is issued on employee table. This results in infinity.

For more information you can read  official documentation on Triggers.

Did you enjoy this post? Then put your comments below. Also you can subscribe here to stay updated on latest posts of

2 thoughts on “PL/SQL Triggers – Explained in detail

  1. hi paras shah
    my name sekhar , I am plsql developer please explain dml triggers,ddl triggers, instead of triggers with examples

    Thanks& regards

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.