SQL Triggers — Automatic Responses to Table Changes

When an exam mark changes, the school wants an audit record of the old mark and the new mark without relying on every application to remember the audit insert.

A trigger is database-side logic that runs automatically for a specified table event, such as an insertion, update or deletion.

MySQL row triggers expose OLD and NEW values according to the event. A BEFORE trigger runs before the row change; an AFTER trigger runs afterward. The trigger is part of the database operation, so its errors and transactional effects need to be considered with the initiating statement.

Related concepts: Data Constraints and Transactions and Handle SQL Errors.

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.

MySQL 8.4 · row trigger

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

MySQL 8.4 · row trigger

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
);

The following seed rows complete the input for the worked examples. They are also reproduced with each example that uses them.

INSERT INTO students VALUES (1,'Aarav Sharma'),(2,'Diya Iyer'),(3,'Sneha Patil');
INSERT INTO results VALUES (1,'Maths',85),(1,'Science',88),(2,'Maths',92),(2,'Science',90),(3,'Maths',78),(3,'Science',84);

MySQL 8.4 · row trigger

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

The inserted row below is a deliberately invalid test. The BEFORE INSERT trigger rejects it; the error is the intended result and should not be reported as a successful insert.

MySQL 8.4 · row trigger

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

MySQL 8.4 · row trigger

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 ;

• 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).

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

Triggers can create hidden work and unexpected dependencies. They are not a substitute for a declarative CHECK when a simple permitted-value rule is sufficient. Engine rules determine recursion, available references and allowed statements.

Use a MySQL AFTER UPDATE trigger to record Diya’s mark correction.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

MySQL 8.4 · row trigger

CREATE TABLE practice_audit (roll_no INT, old_mark INT, new_mark INT);
CREATE TRIGGER practice_mark_audit AFTER UPDATE ON practice_students FOR EACH ROW INSERT INTO practice_audit VALUES (NEW.roll_no, OLD.marks, NEW.marks);
UPDATE practice_students SET marks = 75 WHERE roll_no = 2;
SELECT roll_no, old_mark, new_mark FROM practice_audit;

The update initiates the trigger. OLD.marks records 70 and NEW.marks records 75, making the state change visible in a separate audit row.

Run this engine-specific script on a fresh MySQL 8.4 practice schema with the required routine privileges. It is not executable in SQLite.

A trigger is database-side logic that runs automatically for a specified table event, such as an insertion, update or deletion.

The update initiates the trigger. OLD.marks records 70 and NEW.marks records 75, making the state change visible in a separate audit row.

Continue your understanding with Data Constraints and Transactions and Handle SQL Errors.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.