Types of Triggers

A trigger’s type is defined by the type of triggering transaction and by the level at which the trigger is executed. Oracle has the following types of triggers depending on the different applications:

  • Row Level Triggers
  • Statement Level Triggers
  • Before Triggers
  • After Triggers

Here is a brief description about above triggers:

Row Level Triggers

Row level triggers execute once for each row in a transaction. The commands of row level triggers are executed on all rows that are affected by the command that enables the trigger. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If the triggering statement affects no rows, the trigger is not executed at all. Row level triggers are created using the FOR EACH ROW clause in the CREATE TRIGGER command.

Statement Level Triggers

Statement level triggers are triggered only once for each transaction. For example when an UPDATE command update 15 rows, the commands contained in the trigger are executed only once, and not with every processed row. Statement level trigger are the default types of trigger created via the CREATE TRIGGER command.

Before  Trigger

Since triggers are executed by events, they may be set to occur immediately before or after those events. When a trigger is defined, you can specify whether the trigger must occur before or after the triggering event i.e. INSERT, UPDATE, or DELETE commands.

BEFORE trigger execute the trigger action before the triggering statement. These types of triggers are commonly used in the following situation:

  • BEFORE triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete. By using a BEFORE trigger, you can eliminate unnecessary processing of the triggering statement. For example: To prevent deletion on Sunday, for this we have to use Statement level before trigger on DELETE statement.
  • BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

After Trigger

AFTER trigger executes the trigger action after the triggering statement is executed. AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.

For example: To perform cascade delete operation, it means that user delete the record fro one table, but the corresponding records in other tables are delete automatically by a trigger which fired after the execution of delete statement issued by the user.

When combining the different types of triggering actions, there are mainly 12 possible valid trigger types available to us. The possible configurations are:

  • BEFORE INSERT row
  • BEFORE INSERT statement
  • AFTER INSERT row
  • AFTER INSERT statement
  • BEFORE UPDATE row
  • BEFORE UPDATE statement
  • AFTER UPDATE Row
  • AFTER UPDATE statement
  • BEFORE DELETE row
  • BEFORE DELETE statement
  • AFTER DELETE row
  • AFTER DELETE statement
Name Statement Level Row Level
BEFORE option Oracle fires trigger only once before executing the triggering statement Oracle fires trigger before modifying each row affected by the triggering statement
AFTER option Oracle fires trigger only once before executing the triggering statement Oracle fires trigger before modifying each row affected by the triggering statement

Instead of Trigger

These Triggers are Defined on a View rather than a table.You can use INSTEAD OF TRIGGER to tell oracle what to do instead of  performing actions that invoked the  trigger.

Leave a Reply