SQL Triggers

A trigger is a special type of stored procedure that automatically executes when a specific event occurs on a table. Unlike a regular stored procedure that must be called manually using CALL, a trigger fires on its own in response to a data change — such as inserting, updating, or deleting a row.

Think of a trigger like a motion sensor light. No one manually turns on the light — it activates automatically whenever motion is detected. Similarly, a trigger activates automatically whenever a specified database event takes place.

When Are Triggers Used?

• Automatically logging changes — recording who changed what and when.
• Enforcing complex business rules that cannot be handled by simple constraints.
• Auditing — keeping a history of all changes to critical tables.
• Automatically updating related tables when data changes in another table.
• Validating data before it is saved to the database.

Trigger Structure

Syntax

DELIMITER //

CREATE TRIGGER trigger_name
BEFORE | AFTER   INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- trigger logic here
END //

DELIMITER ;

The Reference Tables

Students Table

AuditLog Table (For Recording Changes)

CREATE TABLE AuditLog (
    LogID       INT AUTO_INCREMENT PRIMARY KEY,
    TableName   VARCHAR(50),
    Action      VARCHAR(10),
    StudentID   INT,
    OldFees     DECIMAL(8,2),
    NewFees     DECIMAL(8,2),
    ChangedAt   DATETIME
);

AFTER INSERT Trigger

This trigger fires automatically after a new student is added to the Students table. It logs the new student's details into the AuditLog.

DELIMITER //

CREATE TRIGGER trg_after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, NewFees, ChangedAt)
    VALUES ('Students', 'INSERT', NEW.StudentID, NEW.Fees, NOW());
END //

DELIMITER ;

-- Inserting a new student will automatically trigger the log entry
INSERT INTO Students (StudentName, Fees) VALUES ('Arjun Nair', 38000);

AuditLog after the INSERT:

The log entry was created automatically — no manual INSERT into AuditLog was needed.

AFTER UPDATE Trigger

This trigger records the old and new fee values whenever a student's fees are updated.

DELIMITER //

CREATE TRIGGER trg_after_fees_update
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    IF OLD.Fees != NEW.Fees THEN
        INSERT INTO AuditLog (TableName, Action, StudentID, OldFees, NewFees, ChangedAt)
        VALUES ('Students', 'UPDATE', NEW.StudentID, OLD.Fees, NEW.Fees, NOW());
    END IF;
END //

DELIMITER ;

-- Updating Ravi's fees
UPDATE Students SET Fees = 48000 WHERE StudentID = 1;

AuditLog entry created automatically:

OLD.Fees holds the fee value before the update; NEW.Fees holds the fee value after the update.

BEFORE INSERT Trigger — Data Validation

A BEFORE trigger runs before the data is actually saved. This allows validating or modifying the data before it reaches the table.

DELIMITER //

CREATE TRIGGER trg_before_student_insert
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    -- Ensure fees are never below 10000
    IF NEW.Fees < 10000 THEN
        SET NEW.Fees = 10000;
    END IF;

    -- Ensure name is stored in uppercase
    SET NEW.StudentName = UPPER(NEW.StudentName);
END //

DELIMITER ;

-- Inserting with a low fee
INSERT INTO Students (StudentName, Fees) VALUES ('Meera Joshi', 5000);

Result in the table: StudentName = 'MEERA JOSHI', Fees = 10000 (adjusted by the trigger)

AFTER DELETE Trigger

This trigger logs the details of a deleted student before the row is removed.

DELIMITER //

CREATE TRIGGER trg_after_student_delete
AFTER DELETE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, OldFees, ChangedAt)
    VALUES ('Students', 'DELETE', OLD.StudentID, OLD.Fees, NOW());
END //

DELIMITER ;

When a student is deleted, OLD.StudentID and OLD.Fees still hold the values of the deleted row, allowing the log entry to be created before the row disappears.

NEW and OLD Reference Table

Managing Triggers

-- View all triggers in the current database
SHOW TRIGGERS;

-- View triggers on a specific table
SHOW TRIGGERS WHERE `Table` = 'Students';

-- Remove a trigger
DROP TRIGGER IF EXISTS trg_after_student_insert;

BEFORE vs AFTER Trigger

Key Points to Remember

• Triggers fire automatically — they require no manual calling.
• NEW refers to the incoming row data; OLD refers to the existing row data before the change.
• Use BEFORE triggers to validate or modify data before it is saved.
• Use AFTER triggers for logging, auditing, and cascading updates to other tables.
• A trigger is tied to a specific table and a specific event (INSERT, UPDATE, or DELETE).
• Overuse of triggers can make a database harder to debug and maintain — use them for genuine automation needs.

Summary

Triggers are automatic database procedures that execute in response to INSERT, UPDATE, or DELETE events on a table. They are powerful tools for auditing changes, enforcing business rules, and keeping related data consistent across tables. Understanding how BEFORE and AFTER triggers work — and how NEW and OLD reference row data — is the foundation of writing effective database triggers.