Triggers | SQL Tutorial

What is a Trigger?

A Trigger is a set of SQL statements that automatically runs (or "fires") when a specific event occurs on a particular table, such as INSERT, UPDATE, or DELETE.

Use Cases

Enforcing complex integrity constraints.
Auditing changes (logging old values).
Automatically updating related tables.

Creating a Trigger

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

Example: Audit Log Trigger

Create a trigger that logs deleted employees to an Audit table before deletion.

DELIMITER //
CREATE TRIGGER BeforeEmployeeDelete
BEFORE DELETE ON Employees FOR EACH ROW
BEGIN
    INSERT INTO Audit (Description, DeletedDate)
    VALUES (CONCAT('Deleted employee: ', OLD.Name), NOW());
END //
DELIMITER ;

OLD keyword: Refers to the row being deleted/updated.
NEW keyword: Refers to the new row being inserted/updated.

sql-create-trigger

Create Insert Trigger

Problem Statement

Create a trigger named LogNewUser that runs AFTER INSERT on the Users table to insert a message 'New user joined' into the Logs table.

Dropping a Trigger

DROP TRIGGER IF EXISTS trigger_name;

[!CAUTION] Triggers can be invisible logic that makes debugging difficult. Use them sparingly.