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.
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.
It is used to mention the execution time of the trigger. It specifies whether the trigger should fire after or before the DML statement.
Triggers can be fired against any of these DML statements.
It is used to indicate the column name on which the trigger will be fired but it used for only update statement.
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.
It is used to indicate the condition for trigger execution.
IT is used for declaring the variable.
In this actual query for trigger is written.
Create Trigger ABC
Before Insert On Students
This trigger is activated when an insert statement is issued, but before the new record is inserted
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
CREATE TRIGGER Amount-CHECK BEFORE UPDATE ON account
FOR EACH ROW
IF (NEW.amount < 0) THEN
SET NEW.amount = 0;
ELSEIF (NEW.amount > 100) THEN
SET NEW.amount = 100;
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