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.

System events
- Database startup and shutdown.
- Server error message events.
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.

create or replace trigger prevent_truncates
  before truncate on schema
  begin
    raise_application_error(-20001, 'TRUNCATE not permitted');
  end;

Trigger created.

create table salary_bk as select * from salary;

Table created.

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

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

Example.2

The below given trigger updates every create statement, which happens in the schema level into the log_table.

CREATE TABLE log_table (
    user_name VARCHAR2(100),
    event_date DATE,
    detail VARCHAR2(400)
);

Table created.

CREATE OR REPLACE TRIGGER log_create_trigg
AFTER CREATE ON SCHEMA
BEGIN
  INSERT INTO log_table
    (user_name, event_date, detail)
  VALUES
    (USER, SYSDATE, 'created object is: ' || ora_dict_obj_name);
END;

Trigger created.

select * from log_table;

No rows are selected.

create table abc as select * from dba_users;

Table created.

col user_name for a12
col detail for a25
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.

CREATE TABLE log_trigger_table (
    user_name VARCHAR2(30),
    event_date DATE,
    action VARCHAR2(300)
);

Table created.

CREATE OR REPLACE TRIGGER logon_trigger
  AFTER LOGON ON SCHEMA
BEGIN
  INSERT INTO log_trigger_table
    (user_name, event_date, action)
  VALUES
    (USER, SYSDATE, 'Logging On');
END;

Trigger created.

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.

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.

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.

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

create table sal_deleted (
    rollno number(5),
    name varchar(15),
    del_date date
);

Table created.

Now create the trigger.

ed sal_delete_trig

Create or replace trigger sal_delete before deleting on salary. For each row, begin.

insert into sal_deleted values
(:old.rollno, :old.empname, sysdate);
END;

@sal_delete_trig

Trigger created.

delete from salary where rollno = 10005;

1 row deleted.

select * from salary;

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.

create table student_details
(
    rollno number(5),
    name varchar(15),
    dob date,
    doj date,
    dop date
);

Table created.

ed student_details_trig;

create trigger student_details_trig
    before insert
    on student_details
    for each row
begin
    :new.doj := sysdate;
end;

@student_details_trig

Trigger created.

select * from student_details;

No rows selected.

select sysdate from dual;

SYSDATE
19-OCT-12

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

1 row created.

select * from student_details;

ROLLNO NAME DOB DOJ DOP
1001 MAHESH 30-OCT-86 19-OCT-12

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.

select * from salary;

create table salupdated (
    rollno number(5),
    empname varchar(15),
    design varchar(15),
    bpay number(8,2),
    da number(6,2),
    total number(8,2),
    ta number(6,2)
);

Table created.

ed salupdate_trig

Create or replace trigger salupdate_trig before update on salary for each row.

insert into salupdated values 
(:old.rollno, :old.empname, :old.design, :old.bpay, :old.da, :old.netsal, :old.ta);
END;

@salupdate_trig

Trigger created.

select * from salupdated;

No rows selected.

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

3 rows updated.

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

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

Example.4

Following DML trigger will raise an Application error, while trying to delete the records belonging to Asst.Manager.

select * from salary;

CREATE OR REPLACE TRIGGER not_del
  AFTER DELETE ON salary
  FOR EACH ROW
BEGIN
  IF :old.DESIGN = 'Asst.Manager' THEN
    raise_application_error(-20015, 'Not Delete this Row');
  END IF;
END;

Trigger created.

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.

Conclusion

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