Triggers:

A trigger is a block of code that is executed automatically from a database statement. Triggers is generally executed for DML statements such as INSERT, UPDATE or DELETE. It resides in a database code and is fired automatically when the database code requires to perform the INSERT ,UPDATE or DELETE statement. It is also stored in the database as stored procedures. It does not involve any COMMIT transaction rather it is a part of a transaction.

SYNTAX :

CREATE OR REPLACE TRIGGER <trigger-name>
[BEFORE/AFTER]
[INSERT/UPDATE/DELETE]
OF<column-name>
ON<table-name>
[REFERENCING OLD AS O NEW AS N]
[FOR EACH ROW]
WHEN <trigger-condition>
DECLARE
BEGIN
<sql-statement>
END;

CREATE OR REPLACE TRIGGER <trigger-name>:

It is used to create a new trigger or replace an existing trigger.

[BEFORE/AFTER]:

It is used to mention the execution time of the trigger. It specifies whether the trigger should fire after or before the DML statement.

[INSERT/UPDATE/DELETE]:

Triggers can be fired against any of these DML statements.

OF<column-name>:

It is used to indicate the column name on which the trigger will be fired but it used for only update statement.

ON<table-name>

It is used to indicate on which table the trigger will be fired

[REFERENCING OLD AS O NEW AS N]:

This is option and used to define the aliases.

[FOR EACH ROW]:

It is used to indicate if the trigger is a row-level trigger or a statement-level trigger.

WHEN <trigger-condition>:

It is used to indicate the condition for trigger execution.

DECLARE:

IT is used for declaring the variable.

BEGIN……END:

In this actual query for trigger is written.

Example:1

Create Trigger ABC

Before Insert On Students

This trigger is activated when an insert statement is issued, but before the new record is inserted

Example:2

Create Trigger XYZ

After Update On Students

This trigger is activated when an update statement is issued and after the update is executed

Let us see the one more example to understand the trigger

Example:

CREATE TRIGGER Amount-CHECK BEFORE UPDATE ON account

FOR EACH ROW

BEGIN

IF (NEW.amount < 0) THEN

SET NEW.amount = 0;

ELSEIF (NEW.amount > 100) THEN

SET NEW.amount = 100;

END IF;

END;

In this the trigger will first check the amount if it is less than zero it will set amount as zero but if it is greater than hundred then it will set amount as hundred