Learn About Different Type Of Triggers In Oracle

Database triggers are specialized stored programs. Oracle engine allow the definition of procedure that are implicitly executed when an insert, update, delete is issued again a table from sql or thought application the trigger automatically associated DML statement is executed.They are not called directly but are triggered by events in the database. They run between the time you issue a commandand the time you perform the database management system action. You can write triggers in PL/SQL.

PL/SQL Type of Triggers based on how they triggered:-

Before Triggers: These triggers are fired before the triggering SQL statement (INSERT, UPDATE, DELETE) is executed. The execution of triggering SQL statement is stopped depending on the various conditions to be fulfilled in 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 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 are fired for each row of DML operation being performed on a table. we can not access the column values for records being inserted, updated, deleted on the table and not individual records.

PL/SQL Triggers Syntax Description:-

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

{BEFORE , AFTER }: Indicates the where should trigger get fired. BEFORE trigger execute before when statement execute before time or AFTER trigger execute after when statement execute after time.

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

ON Table Name: Determine the perform trigger event in selected Table.

[Referencing {old AS old, new AS new}]: Reference the old and new values of the data being changed. :old use to existing row perform and :new use to execute new row to perform. 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.

Note:-

Insert have no :OLD value ( before execution) and have :NEW value (After execution)

Delete have no :OLD value but it have :NEW value.

Update have 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 rows that satisfy the condition specified.

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

1.System events

.....a. Database startup and shutdown

.....b. Server error message events

2.User events

.....a. User logon and logoff

.....b. DDL statements (CREATE, ALTER, and DROP)

.....c. DML statements (INSERT, DELETE, and UPDATE)

Based on the above condition we can classify the trigger into 5 catogory 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 trigger.

1.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 that can be used to get user, client or system information , commonly used are given below.

ORA_CLIENT_IP_ADDRESS	returns the client IP address as varchar2
ORA_DATABASE_NAME	returns database name as varchar2
ORA_DES_ENCRYPTED_PASSWORD	returns DES-encrypted password as varchar2
ORA_DICT_OBJ_NAME	returns object name as varchar2
ORA_DICT_OBJ_NAME_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_OWNER	returns the owner of the object acted upon by the event as a varchar2
ORA_DICT_OBJ_OWNER_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_TYPE	returns the datatype of the dictionary object changed by the event as a varchar2
ORA_GRANTEE	returns the number of elements in the list as a pls_integer
ORA_INSTANCE_NUM	returns the current database instance number as a number
ORA_IS_ALTER_COLUMN	returns true or false depending if the column has been altered (true = altered)
ORA_IS_CREATING_NESTED_TABLE	returns a true or false value when you create a table with a nested table
ORA_IS_DROP_COLUMN	returns true or false depending if the column has been dropped (true = dropped)
ORA_IS_SERVERERROR	returns true or false when the error is on the error stack
ORA_LOGIN_USER	returns the current schema name as a varchar2
ORA_PARTITION_POS	returns the numeric position with the SQL text where you can insert a partition clause
ORA_PRIVILEGE_LIST	returns the number of elements in the list as a pls_integer
ORA_REVOKEE	returns the number of elements in the list as a pls_integer
ORA_SERVER_ERROR	returns the error number as a number
ORA_SERVER_ERROR_DEPTH	returns the number of errors on the error stack as a pls_interger
ORA_SERVER_ERROR_MSG	returns an error message text as a varchar2
ORA_SERVER_ERROR_NUM_PARAMS	returns the count of any substituted strings from error messages as a pls_integer
ORA_SERVER_ERROR_PARAM	returns an error message text as a varchar2
ORA_SQL_TXT	returns the number of elements in the list as a pls_integer
ORA_SYSEVENT	returns the system event that was responible for firing the trigger as a varchar2
ORA_WITH_GRANT_OPTION	returns true or false when privileges are granted with grant option (true = with grant option)
SPACE_ERROR_INFO	returns true or false when the triggering event is related to an out-of-space condition.Now for an example

Example.1

The below given ddl trigger prevent truncating table on schema level,

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.

SQL> create table salary_bk as select * from salary;

Table created.

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

SQL> truncate table salary_bk;

truncate table salary_bk

               *

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 

Below given trigger update every create statement that happens in the schema level into 

log_table,

SQL> CREATE TABLE log_table(

  2     user_name       VARCHAR2(100),

  3     event_date      DATE,

  4     detail          VARCHAR2(400));

Table created.

SQL> 

CREATE OR REPLACE TRIGGER log_create_trigg

  2    AFTER CREATE ON SCHEMA

  3  BEGIN

  4    INSERT INTO log_table

  5      (user_name, event_date, detail)

  6    VALUES

  7      (USER, SYSDATE, 'created object is: ' || ora_dict_obj_name);

  8  END;

  9  /

Trigger created.

SQL> select * from log_table;

no rows selected

SQL> create table abc as select * from dba_users;

Table created.

SQL> col user_name for a12

SQL> col detail for a25

SQL> select * from log_table;

USER_NAME    EVENT_DAT DETAIL

------------ --------- -------------------------

MAHI         19-OCT-12 created object is: ABC

2.Database event trigger

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

Example:-

Below given trigger logs the logging information into log_trigger_table table, 

SQL> CREATE TABLE log_trigger_table (

  2        user_name       VARCHAR2(30),

  3        event_date      DATE,

  4        action     VARCHAR2(300));

Table created.

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

SQL> select * from log_trigger_table;

USER_NAME    EVENT_DAT ACTION

------------ --------- -----------------------------------

MAHI         19-OCT-12 Logging On

3.DML Trigger

These triggers fire when you insert, update, or delete data from a table. You can fire them once for all 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 insert each record that will deleted from salary table into sal_deleted table,

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

SQL> create table sal_deleted(

rollno number(5), name varchar(15),

del_date date);

Table created.
SQL>

Now create the trigger,

SQL>ed sal_delete_trig

create or replace trigger sal_delete before delete

on salary for each row

begin 

insert into sal_deleted values

 (:old.rollno, :old.empname,sysdate);

end;

/

SQL> @sal_delete_trig

Trigger created.

SQL> delete from salary where rollno = 10005;

1 row deleted.

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> select * from sal_deleted;

    ROLLNO NAME            DEL_DATE

---------- --------------- ---------

     10005 Arunkumar.K     19-OCT-12

Example.2

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

SQL> create table student_details

  2  (rollno number(5), name varchar(15),

  3  dob date, doj date, dop date );

Table created.

SQL> ed student_details_trig;

create trigger student_details_trig before insert

on student_details for each row

begin

:new.doj := sysdate;

end;

/

SQL> @student_details_trig

Trigger created.

SQL> select * from student_details;

no rows selected

SQL> select sysdate from dual;

SYSDATE

---------

19-OCT-12

SQL> insert into student_details (rollno,name,dob) values (1001,'MAHESH','30-OCT-86');

1 row created.

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 records into salupdated table before update happens in salary table,

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

SQL> ed salupdate_trig

create or replace trigger salupdate_trig before update

on salary for each row

begin

insert into salupdated values

(:old.rollno, :old.empname, :old.design, :old.bpay, :old.da, :old.netsal, :old.ta);

end;

/

SQL> @salupdate_trig

Trigger created.

SQL> select * from salupdated;

no rows selected

SQL> update salary set BPAY=21000 where DESIGN='Asst.Manager';

3 rows updated.

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

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 records belonging to Asst.Manager,

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

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.

SQL> delete from salary where rollno=10004;

delete from salary where rollno=10004

            *

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