PL/SQL - Types of Triggers, ROW, Statement, Before and After, INSTEAD OF

Types of Triggers PL/SQL

Types of Triggers

The various types of triggers are as follows:

1) Row Triggers: A row trigger is fired each time the table is the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired Once for row affected by the UPDATE statement. If the triggering affects no rows, the trigger is not executed at all. Row trigger used when the trigger action code depends on the data provided triggering, statement or rows that are affected.

For example, if the trigger is keeping the track of all the affected records.

2) Statement Triggers: A row trigger is fired once on behalf of the triggering statement, independent of the number of rows the statement affects (even if no rows are affected). Statement useful if the code in the trigger action does not depend on the provided by the triggering statement or the rows affected.

For example, if the trigger makes the security check on the time or the

3) Before and After Triggers: When defining a trigger one can the trigger timing, i.e. to specify when the triggering action is to executed in relation to the triggering statement. Before and After to both row and the statement triggers.

The two of them are described as follows:

i) Before Triggers: BEFORE triggers execute the trigger action before the triggering statement.

These types of triggers are commonly used in the following situation:

a) 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 triggers.

b) Before Statement are  used to derive specific column values before completing a triggering INSERT or UPDATE statement.

ii) After Triggers: AFTER trigger executes the trigger action after the triggering statement is executed. These types of triggers are commonly used in the following situation:

a) AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.

b) If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering Statement.


4) INSTEAD OF  Triggers: The INSTEAD OF triggers are the written specially for modifying views, which cannot be directly modified through SQL DMIL, statements. As you know that a view virtual table that draws its data from some underlying base INSTEAD OF triggers, the UPDATE, ELETE INSERT can be directly performed on the underlying tables.

The INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through SQL DML statements.