Types of Triggers in SQL

Triggers in SQL:

A trigger has defined the type of triggering transaction and the level at which the trigger is executed.

Types of Triggers:

There are five types of triggers:
i. Row-level Triggers
ii. Statement level Triggers
iii. BEFORE and AFTER Triggers
iv. INSTEAD OF Triggers

Row-level Triggers:

These triggers are executed once for each row in a transaction. It is the most common type of trigger. They are mostly used in data auditing applications. It is also useful for keeping distributed data in sync.

Statement level Triggers:

These triggers are executed once for each transaction. This type of trigger is not often used for data-related activities. They are normally used to enforce additional security measures on the types of transactions that may be performed on a table.

BEFORE and AFTER Triggers:

BEFORE Triggers: These triggers execute the trigger action before the triggering statement. These types of triggers are commonly used in the following situations:
i. When the trigger action should determine whether or not the triggering statement should be allowed to be completed. In this way, you can eliminate unnecessary processing of the triggering statement.
ii. When we want to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers:
These triggers execute the trigger action after the triggering statement is executed. These types of triggers are commonly used in the following situations:

i. When you want the triggering statement to complete before executing the trigger action.
ii. In auditing applications, they do not fire until the row has been modified.
iii. If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

INSTEAD OF Triggers:

This trigger is used when we want to tell Oracle what to do instead of performing the actions that involved the trigger.