SQL Triggers - Automatic Responses to Table Changes

Introduction

Imagine you want to automatically log when a student's result is updated — without writing extra SQL every time. That’s exactly what triggers do. They’re special procedures that run in response to events like INSERT, UPDATE, or DELETE — and they do so automatically.

What is a Trigger?

A trigger is a named set of SQL statements that automatically executes when a specific table event occurs. Triggers help with auditing, validation, or enforcing rules behind the scenes.

Trigger Syntax (MySQL Style)

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

Sample Tables – students, results, result_audit

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

CREATE TABLE result_audit (
  audit_id INT AUTO_INCREMENT PRIMARY KEY,
  roll_no INT,
  subject VARCHAR(30),
  old_marks INT,
  new_marks INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. Trigger to Track Marks Updates

This trigger logs any change to marks in the results table into the result_audit table.

DELIMITER $$

CREATE TRIGGER after_marks_update
AFTER UPDATE ON results
FOR EACH ROW
BEGIN
  IF OLD.marks != NEW.marks THEN
    INSERT INTO result_audit (roll_no, subject, old_marks, new_marks)
    VALUES (OLD.roll_no, OLD.subject, OLD.marks, NEW.marks);
  END IF;
END$$

DELIMITER ;

Update example:

UPDATE results
SET marks = 90
WHERE roll_no = 1 AND subject = 'Maths';

Audit table output:

audit_id | roll_no | subject | old_marks | new_marks | updated_at
---------+---------+---------+-----------+-----------+---------------------
1        | 1       | Maths   | 85        | 90        | 2024-05-24 10:12:33

2. BEFORE INSERT Trigger – Validate Marks

Prevent insertion of marks greater than 100:

DELIMITER $$

CREATE TRIGGER check_marks_before_insert
BEFORE INSERT ON results
FOR EACH ROW
BEGIN
  IF NEW.marks > 100 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Marks cannot exceed 100';
  END IF;
END$$

DELIMITER ;

Try this:

INSERT INTO results VALUES (2, 'Science', 105);

ERROR 1644 (45000): Marks cannot exceed 100

3. BEFORE DELETE Trigger – Prevent Deletion

Stop deletion of results for a topper:

DELIMITER $$

CREATE TRIGGER protect_topper
BEFORE DELETE ON results
FOR EACH ROW
BEGIN
  IF OLD.marks >= 90 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot delete result of a topper';
  END IF;
END$$

DELIMITER ;

Best Practices

• Use AFTER triggers for audit logging.
• Use BEFORE triggers for validations.
• Avoid complex logic inside triggers to keep them fast and safe.
• Name triggers meaningfully (e.g., before_insert_check, log_updates).

Real-World School Use Cases

• Log every marks update in an audit table
• Prevent incorrect entries (e.g., marks above 100)
• Track deleted student records for future reference

Summary

Triggers act like silent protectors in your database — enforcing rules, logging changes, and blocking bad data — all without writing manual queries every time. For school databases or large systems, triggers help maintain consistency and automate monitoring.

What’s Next?

Coming up: SQL Transactions — group multiple statements into a single, all-or-nothing operation.