Oracle

Learn About Different Type Of Triggers In Oracle

Overview

Database triggers are specialized stored programs. Oracle engine allows the definition of the procedures, which are implicitly executed when an insert, update, or delete is issued in a table from SQL or through the Application, and the trigger automatically executes a DML statement.They are not called directly, but are triggered by the events in the database. They run between the time, when you issue a command and the time, you perform the database management system action. You can write the triggers in PL/SQL.

Introduction

PL/SQL Type of Triggers are based on how they are triggered.

Before Triggers: These triggers are fired before  the SQL statement  trigger (INSERT, UPDATE, DELETE) is executed. The execution of  the triggering SQL statement is stopped, depending on the various conditions to be fulfilled in the BEFORE trigger.

After Triggers: These triggers are fired after the triggering SQL statement (INSERT, UPDATE, DELETE) is executed. The triggering SQL statement is executed first, followed by the code of the trigger.

ROW Trigger: The triggers are fired for each and every record, which is inserted or updated or deleted from a table.

Statement Trigger: The trigger is fired for each row of the DML operation, being performed on a table. We cannot access the column values for the records being inserted, updated, deleted on the table nor the individual records.

PL/SQL Triggers Syntax Description

CREATE or REPLACE TRIGGER trigger_name: Creates a trigger with the given name, else overwrites an existing trigger with the same name.

{BEFORE , AFTER }: Indicates where should trigger be fired. BEFORE trigger executes before when statement executes before time or AFTER trigger executes, after when statement executes after time.

{INSERT , UPDATE , DELETE}: Determines the performing trigger event. More than one triggering events can be used together, separated by OR keyword.

ON Table Name: Determines the performed trigger event in the selected table.

[Referencing {old AS old, new AS new}]: Reference the old and new values of the data, being changed. : old is used for existing row to perform and : new is used to execute a new row to perform. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference the old values, when inserting a record or new values, or when deleting a record, because they do not exist.

Note

Insert has no :OLD value (before execution) and have : NEW value (After execution).
Delete has no : OLD value but it has :NEW value.
Update has both : OLD and : NEW value.
for each row: Trigger must fire, when each row gets affected (Row Level Trigger) or just once, when the entire SQL statement is executed (Statement Level trigger).
WHEN (condition): Valid only for row level triggers. The trigger is fired only for the rows, which satisfy the specified condition.

There are various events on which a trigger can be written, such as:

  1. System events
    • Database startup and shutdown.
    • Server error message events.
  2. User events
    • User login and logoff.
    • DDL statements (CREATE, ALTER, and DROP).
    • DML statements (INSERT, DELETE, and UPDATE).

Based on the above condition, we can classify the trigger into five categories: DML trigger, DDL trigger, Compound triggers, Instead-Of triggers and System or database event triggers. Out of which, here I am discussing mainly DDL and DML triggers.

DDL Trigger

DDL triggers fire, when you create, change or remove objects in a database. They support both before and after event triggers and work at the database or schema level.

DDL event supported

alter, analyze, associate statistics, audit, comment, create, DDL, disassociate statistics, drop, grant, noaudit, rename, revoke, truncate .

There are a number of event attribute functions, which can be used to get user, client or system information, commonly used ones are given below:

table

Example.1

The given DDL trigger prevents truncating the table on the schema level.

  1. SQL> create or replace trigger prevent_truncates  
  2.  before truncate on schema  
  3.  begin  
  4.  raise_application_error(-20001,'TRUNCATE not permitted');  
  5.  end;  
  6.  /  
Trigger created.
  1. SQL> create table salary_bk as select * from salary;  
Table created.
  1. SQL> select * from salary_bk;  
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
  1. SQL> truncate table salary_bk;  
  2. truncate table salary_bk  
  3. *  
ERROR at line 1

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TRUNCATE not permitted
ORA-06512: at line 2
SQL>

Example.2

The below given trigger updates every create statement, which happens in the schema level into the log_table.
  1. SQL> CREATE TABLE log_table(  
  2.  user_name VARCHAR2(100),  
  3.  event_date DATE,  
  4.  detail VARCHAR2(400));  
Table created.
  1. SQL>   
  2. CREATE OR REPLACE TRIGGER log_create_trigg  
  3.  AFTER CREATE ON SCHEMA  
  4.  BEGIN  
  5.  INSERT INTO log_table  
  6.  (user_name, event_date, detail)  
  7.  VALUES  
  8.  (USER, SYSDATE, 'created object is: ' || ora_dict_obj_name);  
  9.  END;  
  10.  /  
Trigger created.
  1. SQL> select * from log_table;  
No rows are selected.
  1. SQL> create table abc as select * from dba_users;  
Table created.
  1. SQL> col user_name for a12  
  2. SQL> col detail for a25  
  3. SQL> select * from log_table;  
USER_NAME EVENT_DAT DETAIL
------------ --------- -------------------------
MAHI 19-OCT-12 created object is: ABC

Database event trigger

These triggers fire, when a system activity occurs in the database like the login and logoff event triggers. They are useful for auditing the information of the system access. These triggers, allow you to track the system events and map them to the users.

Example

Below given trigger logs the logging information into log_trigger_table. 
  1. SQL> CREATE TABLE log_trigger_table (  
  2.  user_name VARCHAR2(30),  
  3.  event_date DATE,  
  4.  action VARCHAR2(300));  
Table created.
  1. SQL> CREATE OR REPLACE TRIGGER logon_trigger  
  2.  AFTER LOGON ON SCHEMA  
  3.  BEGIN  
  4.  INSERT INTO log_trigger_table  
  5.  (user_name, event_date, action )  
  6.  VALUES  
  7.  (USER, SYSDATE, 'Logging On');  
  8.  END;  
  9.  /  
Trigger created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options.

C:\Users\DELL\node1>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 19 17:39:19 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: mahi
Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options:
  1. SQL> select * from log_trigger_table;  
USER_NAME EVENT_DAT ACTION
------------ --------- -----------------------------------
MAHI 19-OCT-12 Logging On

DML Trigger

These triggers fire, when you insert, update or delete the data from a table. You can fire them once for all the changes on a table or for each row change, using statement- or row-level trigger types, respectively. DML triggers are useful to control DML statements. You can use these triggers to audit, check, save and replace values before they are changed.

Example.1

Below given example inserts each record, which will be deleted from salary table into sal_deleted table.
  1. SQL> select * from salary;  
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 20000 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 20000 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 20000 1500 1200 1800 22000
10005 Arunkumar.K Programmer 12000 1440 1320 1080 13800
  1. SQL> create table sal_deleted(  
  2. rollno number(5), name varchar(15),  
  3. del_date date);  
Table created.
 
SQL>

Now create the trigger.

SQL>ed sal_delete_trig

Create or replace trigger sal_delete before deleting on salary. For each row, begin:
  1. insert into sal_deleted values  
  2. (:old.rollno, :old.empname,sysdate);  
  3. end;  
  4. /  
  5. SQL> @sal_delete_trig  
Trigger created.
  1. SQL> delete from salary where rollno = 10005;  
1 row deleted.
  1. SQL> select * from salary;  
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 20000 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 20000 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 20000 1500 1200 1800 22000

SQL>
  1. select * from sal_deleted;  
ROLLNO NAME DEL_DATE
---------- --------------- ---------
10005 Arunkumar.K 19-OCT-12

Example.2

The following trigger will insert the system time automatically into DOJ field, while inserting the records into student_details table.

SQL>
  1. create table student_details  
  2.   
  3. 2 (rollno number(5), name varchar(15),  
  4. 3 dob date, doj date, dop date );  
Table created.
  1. SQL> ed student_details_trig;  
  2.   
  3. create trigger student_details_trig before insert  
  4. on student_details for each row  
  5. begin  
  6. :new.doj := sysdate;  
  7. end;  
  8. /  
  9. SQL> @student_details_trig  
Trigger created.
  1. SQL> select * from student_details;  
No rows selected
  1. SQL> select sysdate from dual;  
SYSDATE
---------
19-OCT-12
  1. SQL> insert into student_details (rollno,name,dob) values (1001,'MAHESH','30-OCT-86');  
1 row created.
  1. SQL> select * from student_details;  
ROLLNO NAME DOB DOJ DOP
---------- --------------- --------- --------- ---------
1001 MAHESH 30-OCT-86 19-OCT-12
SQL>

Here, you can see DOJ is automatically inserted by the trigger.

Example.3

Following trigger will insert each record into salupdated table before the update happens in salary table,
  1. SQL> select * from salary;  
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 20000 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 20000 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 20000 1500 1200 1800 22000
  1. SQL> create table salupdated(  
  2.  rollno number(5),  
  3.  empname varchar(15),  
  4.  design varchar(15),  
  5.  bpay number(8,2),  
  6.  da number(6,2),  
  7.  total number(8,2),  
  8.  ta number(6,2));  
Table created.
  1. SQL> ed salupdate_trig  
create or replace trigger salupdate_trig before update on salary for each row,
  1. insert into salupdated values (:old.rollno, :old.empname, :old.design, :old.bpay, :old.da, :old.netsal, :old.ta);  
  2. end;  
  3. /  
  4. SQL> @salupdate_trig  
Trigger created.
  1. SQL> select * from salupdated;  
no rows selected
  1. SQL> update salary set BPAY=21000 where DESIGN='Asst.Manager';  
3 rows updated.
  1. SQL> select * from salary;  
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 21000 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 21000 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 21000 1500 1200 1800 22000
  1. SQL> select * from salupdated;  
ROLLNO EMPNAME DESIGN BPAY DA TOTAL TA
---------- --------------- --------------- ---------- ---------- ---------- ----------
10002 K.K.Omana Asst.Manager 20000 1500 22000 1200
10003 Anishkumar.K Asst.Manager 20000 1500 22000 1200
10004 Girishkumar.K Asst.Manager 20000 1500 22000 1200

SQL>

Example.4

Following DML trigger will raise an Application error, while trying to delete the records belonging to Asst.Manager. 
  1. SQL> select * from salary;  
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
  1. SQL> CREATE or REPLACE TRIGGER not_del  
  2.  AFTER  
  3.  DELETE ON salary  
  4.  for each row  
  5.   
  6.  BEGIN  
  7.  IF :old.DESIGN = 'Asst.Manager' THEN  
  8.  raise_application_error(-20015, 'Not Delete this Row');  
  9.  END IF;  
  10.  END;  
  11.  /  
Trigger created.
  1. SQL> delete from salary where rollno=10004;  
  2. delete from salary where rollno=10004  
  3. *  
ERROR at line 1:
ORA-20015: Not Delete this Row
ORA-06512: at "MAHI.NOT_DEL", line 3
ORA-04088: error during execution of trigger 'MAHI.NOT_DEL

Conclusion: This was all about the triggers in Oracle. I hope this article was helpful. 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs