With the previous tutorial we finished the series of Iterative statements or looping statements in Oracle PL/SQL so now it’s time to move on to a little more advance topic in PL/SQL. Thus 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 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 Events such 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.

types of triggers in pl/sql by manish sharma

  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.

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

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

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

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


{BEFORE|AFTER} Triggering_event ON table_name
[FOLLOWS another_trigger_name]
[WHEN condition]
  declaration statements
  executable statements
  exception-handling statements

For the detailed explanation of the syntax I would suggest you to watch the video tutorial. There I have explained each and every clause of the syntax in detail.

Uses of triggers.

  1. Using triggers we can enforce business rules that can’t be defined by using integrity constants.
  2. Using triggers we can gain strong control over the security.
  3. We can also collect statistical information on the table access.
  4. We can automatically generate values for derived columns such as auto increment numeric primary key.
  5. Using triggers you can prevent invalid transaction.

Restriction on Triggers

  1. Maximum size of the trigger body must not exceed 32,760 bytes because triggers’ bodies are stored in LONG datatypes columns.
  2. A trigger may not issue transaction control statements or TCL statements such as COMMIT, ROLLBACK or SAVEPOINT. All operations performed when the trigger fires, become part of a transaction. Therefore whenever this transaction is rolled back or committed it leads to the respective rolling back or committing of the operations performed. 
  3. Any function or procedure called by a trigger may not issue a transactional control statement unless it contains an autonomous transaction.
  4. Declaring LONG or LONG RAW variable is not permissible in the body of the trigger.

This blog is written keeping in mind the perspective of Certification Exam and Interview. Hope you enjoyed reading. Don’t forget to share this blog on your social media and help others in learning. Thanks & have a great day.