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

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.

{ INSERT [OR] | UPDATE [OR] | DELETE }
– 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

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

Disable Trigger

Enable Trigger

Rename Trigger

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

2) Create table employee_history

3) Create trigger salary_history_trigger and execute it:

4) Update salary in table employee

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

Structure of USER_TRIGGERS

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 OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

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
    sekhar

Leave a Reply

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