SQL Triggers

What is a Trigger?

A trigger is a set of SQL statements that automatically execute or fire when certain events occur in the database. Triggers are used to enforce data integrity, automate processes, and maintain historical data.

Types of Triggers

• BEFORE Trigger: Executes before an insert, update, or delete operation.
• AFTER Trigger: Executes after an insert, update, or delete operation.
• INSTEAD OF Trigger: Executes in place of an insert, update, or delete operation.

Syntax of Creating a Trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

This syntax creates a trigger that executes the specified SQL statements when the defined event occurs on the specified table.

Example of a Trigger

Here’s an example of a trigger that automatically updates the last_modified field of a table whenever a record is updated:

CREATE TRIGGER UpdateLastModified
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;

This trigger sets the last_modified field to the current timestamp whenever an employee record is updated.

Use Cases of Triggers

Triggers are commonly used for:

• Enforcing business rules.
• Validating input data.
• Maintaining audit trails of changes.
• Automatically updating or populating fields.

Practice Exercise

Create a trigger for a sample table to enforce a business rule. For example, automatically set a field when a record is inserted or updated.