Oracle PL/SQL Triggers

Introduction Of Triggers

Today’s tutorial is about Introduction of Triggers in Oracle PL/SQL. In this tutorial I will try to explain you the concepts of triggers, and try to give you the answers of almost all the questions which you might face in your Certification Exam or in your Interview.

Definition of Triggers in Database

Triggers are named PL/SQL blocks which are stored in the database or we can also say that they are specialized stored programs which execute implicitly when a triggering event occurs which means we cannot call and execute them directly instead they only get triggered by events in the database.

Events Which Fires the Triggers

These events can be anything such as

1. A DML Statement. For example Update, Insert or Delete, executing on any table of your database. You can program your trigger to execute either BEFORE or AFTER executing your DML statement. For example you can create a trigger which will get fired Before the Update statement. Or you can create a trigger which will get triggered after the execution of your INSERT DML statement.
2. Next type of triggering statement can be a DDL Statement such as CREATE or ALTER. These triggers can also be executed either BEFORE or AFTER the execution of your DDL statement. These triggers are generally used by DBAs for auditing purposes and they really come in handy when you want to keep an eye on the various changes on your schema such as who created the object or which user. Just like some cool spy tricks.
3. A system event. Yes, you can create a trigger on a system event and by system event I mean shut down or startup of your database.
4. Another type of triggering event can be User Eventssuch as log off or log on onto your database. You can create a trigger which will either execute before or after the event and record the information such as time of event occur, the username who created it.

 Types of Triggers

There are 5 types of triggers in oracle database in which 3 of them are based on the triggering event which are discussed in the previous section.

1. Data Manipulation Language Triggers or DML triggers

As the name suggests these are the triggers which depend on DML statements such as Update, Insert or Delete and they get fired either before or after them. Using DML trigger you can control the behavior of your DML statements. You can audit, check, replace or save values before they are changed. Automatic Increment of your Numeric primary key is one of the most frequent tasks of these types of triggers.

2. Data Definition Language Triggers or DDL triggers.

Again as the name suggests these are the type of triggers which are created over DDL statements such as CREATE or ALTER and get fired either before or after execution of your DDL statements. Using this type of trigger you can monitor the behavior and force rules on your DDL statements.

3. System or Database Event triggers.

Third type of triggers is system or database triggers. These are the type of triggers which come into action when some system event occurs such as database log on or log off. You can use these triggers for auditing purposes for example keeping an eye on information of system access like say who connects with your database and when. Most of the time System or Database Event triggers work as Swiss Knife for DBAs and help them in increasing the security of the data.

4. Instead-of Trigger

This is a type of trigger which enables you to stop and redirect the performance of a DML statement. Often this type of trigger helps you in managing the way you write to non-updatable views. You can also see the application of business rules by INSTEAD OF triggers where they insert, update or delete rows directly in tables that are defining updatable views. Alternatively, sometimes the INSTEAD OF triggers are also seen inserting, updating or deleting rows in designated tables that are otherwise unrelated to the view.

5. Compound triggers

These are multi-tasking triggers that act as both statement as well as row-level triggers when the data is inserted, updated or deleted from a table. You can capture information at four timing points using this trigger:
(a) before the firing statement;
(b) prior to change of each row from the firing statement;
(c) post each row changes from the firing statement;
(d) after the firing statement.
All these types of triggers can be used to audit, check, save and replace the values even before they are changed right when there is a need to take action at the statement as well as row event levels.

Syntax

 CREATE [OR REPLACE] TRIGGER Ttrigger_name
 {BEFORE|AFTER} Triggering_event ON table_name
 [FOR EACH ROW]
 [FOLLOWS another_trigger_name]
 [ENABLE/DISABLE]
 [WHEN condition]
 DECLARE
  declaration statements
 BEGIN
  executable statements
 EXCEPTION
  exception-handling statements
 END;

For the detailed explanation of the syntax I would suggest you to visit my earlier post about triggers. There I have explained each and every clause of the syntax in detail.