Trigger in DBMS

Trigger in DBMS: A trigger is a procedure of SQL statements, which is automatically fired when the DML statements are executed on the table of the database. Triggers are the event-driven procedures, which are managed and stored by the database management system.

Trigger helps in maintaining data integrity by changing the database data in a systematic way. Triggers are always associated with the insert or update or delete command of the database table.

Syntax of Trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT [ OR ] | UPDATE [ OR ] | DELETE}
[OF name_of_column]
ON name_of_the_table 
REFERENCING [ OLD AS old | NEW AS new ] 
[ FOR EACH ROW | FOR EACH STATEMENT ]  
WHEN (condition)
DECLARE
[ declaration_section ]  
 BEGIN   
  --- sql statements 
  END;

Explanation of each statement is described below:

CREATE [OR REPLACE ] TRIGGER trigger_name:

This statement in the syntax creates a trigger in the database with the specified name. It also overwrites the trigger, which exists with the same name.

{ BEFORE | AFTER | INSTEAD OF }:

BEFORE or AFTER statement in the syntax indicates at what time the trigger is fired on the table.

For example, users can use Trigger Before or After inserting data into the table, Before or After updating the data of the table, Before or After deleting the data from the table. INSTEAD OF is a statement, which is used for creating the Trigger on the view. 

{ INSERT [ OR ] | UPDATE [ OR ] | DELETE }:

This statement in the syntax specifies the DML operations. 

[OF col_name] :

This statement in the syntax is only used when the update operation is triggered.

ON table_name  :

This statement in the syntax specifies the table name on which DML operation is to be applied.

REFERENCING [ OLD AS old | NEW AS new ]  :

It is a statement that allows the user to provide the new value by replacing the old value. By default, we have to reference the values in the following form:

:old.column_name

:new.column_name

[ FOR EACH ROW  ]:

This statement in the syntax specifies the row-level triggered.

WHEN (condition):

It is a clause that specifies the condition to be applied to the database data. It is triggered only for those rows which satisfy the condition.

Declare, Begin Body :

Both statements contain the queries and SQL statements to be executed when the trigger is called.

Types of Trigger

The trigger can be categorized into the following three types:

1. Statement Level Trigger

2. Row Level Trigger

3. Before Trigger

4. After Trigger

Row Level Trigger

Row-level Trigger is that trigger that executes once for each table row. This type of trigger always uses the FOR EACH ROW clause in the trigger procedure.

Statement Level Trigger

Statement Level Trigger is that trigger that executes only one time for each statement. In this type of trigger, FOR EACH ROW clause is not present, i.e., it is omitted.

Before Trigger

'Before Trigger' is called before the execution of the DML operations on the database table.

After Trigger

'After Trigger' is called or fired after the execution of DML operations on the database table.