SQL triggers are special types of stored procedures that are executed automatically by the database management system (DBMS) in response to certain events or actions, such as INSERT, UPDATE, DELETE, or SELECT statements. Triggers are used to enforce business rules, constraints, and policies on the data in a database, and they are used to maintain the integrity, consistency, and security of the data in a database.
Triggers are defined using the CREATE TRIGGER statement, which is provided by the DBMS, and they are associated with a table or a view in the database. Triggers are executed automatically by the DBMS when the specified event or action occurs on the associated table or view, and they are executed before or after the event or action, depending on the type of trigger.
There are two main types of triggers:
- DML triggers: DML triggers are triggers that are executed in response to data manipulation language (DML) statements, such as INSERT, UPDATE, DELETE, or SELECT statements. DML triggers are used to enforce business rules, constraints, and policies on the data in a database, and they are used to maintain the integrity, consistency, and security of the data in a database. DML triggers are classified into two types: INSTEAD OF triggers and AFTER triggers.
- INSTEAD OF triggers: INSTEAD OF triggers are DML triggers that are executed instead of the DML statement that fired the trigger, and they are used to modify the data in the database in a different way than the DML statement that fired the trigger. INSTEAD OF triggers are used to implement complex logic or operations that cannot be implemented using the DML statement that fired the trigger.
- AFTER triggers: AFTER triggers are DML triggers that are executed after the DML statement that fired the trigger, and they are used to perform additional actions or tasks based on the data in the database after the DML statement has completed. AFTER triggers are used to implement complex logic or operations that depend on the data in the database after the DML statement has completed.
- DDL triggers: DDL triggers are triggers that are executed in response to data definition language (DDL) statements, such as CREATE, ALTER, DROP, or TRUNCATE statements. DDL triggers are used to enforce business rules, constraints, and policies on the schema or structure of a database, and they are used to maintain the integrity, consistency, and security of the schema or structure of a database.
Triggers are defined using the CREATE TRIGGER statement, which has the following syntax:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {event} ON {table | view}
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
-- trigger body
END;
The CREATE TRIGGER statement is used to define a new trigger in the database, and it consists of the following clauses:
- trigger_name: The trigger_name clause specifies the name of the trigger.
- BEFORE | AFTER: The BEFORE or AFTER clause specifies when the trigger is executed, before or after the event or action that fired the trigger.
- event: The event clause specifies the type of event or action that fires the trigger, such as INSERT, UPDATE, DELETE, or SELECT.
- table | view: The table or view clause specifies the name of the table or view that the trigger is associated with.
- FOR EACH ROW: The FOR EACH ROW clause