MySQL Triggers

A trigger is a stored database program that automatically runs when a specified event occurs.

For example, when a new student is inserted into a students table, a trigger can automatically create an entry in an activity log.

INSERT INTO students
(name, email)
VALUES
('Amit Kumar', 'amit@example.com');

A trigger associated with the students table can automatically execute additional SQL when this INSERT occurs.

Triggers are useful when a database needs to automatically perform an action after or before a table event.

• Maintain audit logs.
• Automatically record changes.
• Maintain related information.
• Validate or transform values in suitable situations.
• Automatically update summary or history tables.
• Track INSERT, UPDATE, and DELETE operations.

MySQL triggers can be associated with three main DML events:

• INSERT – when a new row is inserted.
• UPDATE – when an existing row is updated.
• DELETE – when a row is deleted.

These events determine when the trigger executes.

A BEFORE trigger executes before the triggering INSERT, UPDATE, or DELETE operation takes effect.

Example:

CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    SET NEW.name = TRIM(NEW.name);
END;

This can be useful when a value needs to be adjusted before it is stored.

An AFTER trigger executes after the triggering INSERT, UPDATE, or DELETE operation occurs successfully.

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (NEW.student_id, 'INSERT');
END;

AFTER triggers are commonly used for logging related changes.

The general syntax is:

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

A trigger must specify its timing, event, target table, and action.

Because a trigger can contain multiple SQL statements, the delimiter is commonly changed while creating it.

DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (NEW.student_id, 'INSERT');
END //

DELIMITER ;

The delimiter is changed back after the trigger is created.

MySQL triggers are defined with FOR EACH ROW.

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (NEW.student_id, 'INSERT');
END;

This means the trigger action is executed for each row affected by the triggering statement.

The NEW keyword refers to the new row values associated with an INSERT or UPDATE trigger.

NEW.student_id
NEW.name
NEW.email

For example:

INSERT INTO student_logs(student_id, action)
VALUES (NEW.student_id, 'New student added');

The OLD keyword refers to the existing row values associated with an UPDATE or DELETE trigger.

OLD.student_id
OLD.name
OLD.email

It is useful when recording the values that existed before a change.

Suppose we have an activity log table:

CREATE TABLE student_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    action VARCHAR(100),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create an AFTER INSERT trigger:

DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (NEW.student_id, 'Student Added');
END //

DELIMITER ;

Insert a new student:

INSERT INTO students
(name, email)
VALUES
('Priya Singh', 'priya@example.com');

Then check the log:

SELECT *
FROM student_logs;

The trigger automatically adds a log record.

A BEFORE INSERT trigger can modify appropriate NEW values before the row is inserted.

DELIMITER //

CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    SET NEW.name = TRIM(NEW.name);
END //

DELIMITER ;

If the inserted name contains extra spaces at the beginning or end, TRIM() removes them before storage.

A BEFORE UPDATE trigger can inspect or modify NEW values before an UPDATE is completed.

DELIMITER //

CREATE TRIGGER before_student_update
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
    SET NEW.name = TRIM(NEW.name);
END //

DELIMITER ;

This can help keep stored values in a consistent format.

AFTER UPDATE triggers are useful for maintaining history or audit records.

DELIMITER //

CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (NEW.student_id, 'Student Updated');
END //

DELIMITER ;

UPDATE triggers can compare OLD and NEW values.

DELIMITER //

CREATE TRIGGER after_student_city_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN

    IF OLD.city <> NEW.city THEN

        INSERT INTO student_logs
        (student_id, action)
        VALUES
        (NEW.student_id, 'City Changed');

    END IF;

END //

DELIMITER ;

OLD.city represents the previous city, while NEW.city represents the updated city.

DELETE triggers can use OLD values to record information about a deleted row.

DELIMITER //

CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (OLD.student_id, 'Student Deleted');
END //

DELIMITER ;

Because the row no longer exists after DELETE, OLD is used to access its previous values.

A BEFORE DELETE trigger executes before a row is deleted.

DELIMITER //

CREATE TRIGGER before_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs(student_id, action)
    VALUES (OLD.student_id, 'Student Will Be Deleted');
END //

DELIMITER ;

This can be used for suitable validation or logging operations.

You can view triggers in the current database using:

SHOW TRIGGERS;

You can also filter the results:

SHOW TRIGGERS
WHERE `Table` = 'students';

You can view the SQL used to create a trigger using:

SHOW CREATE TRIGGER after_student_insert;

This is useful for inspecting an existing trigger definition.

Use DROP TRIGGER to remove an existing trigger.

DROP TRIGGER after_student_insert;

After it is dropped, the trigger will no longer execute automatically.

To avoid an error when a trigger does not exist, use:

DROP TRIGGER IF EXISTS after_student_insert;

This is useful when recreating triggers during development.

A trigger definition specifies one timing and one event.

For example:

AFTER INSERT ON students

Another trigger can be created for:

AFTER UPDATE ON students

And another for:

AFTER DELETE ON students

Each trigger is associated with a specific event and timing.

Triggers are commonly used to maintain audit information.

CREATE TABLE student_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    action VARCHAR(50),
    old_city VARCHAR(100),
    new_city VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

An UPDATE trigger can record both old and new values:

DELIMITER //

CREATE TRIGGER audit_student_city
AFTER UPDATE ON students
FOR EACH ROW
BEGIN

    IF NOT (OLD.city <=> NEW.city) THEN

        INSERT INTO student_audit
        (student_id, action, old_city, new_city)
        VALUES
        (NEW.student_id, 'CITY UPDATE',
         OLD.city, NEW.city);

    END IF;

END //

DELIMITER ;

The NULL-safe comparison operator <=> helps correctly compare values when NULL is possible.

• Forgetting to change the delimiter while defining multi-statement triggers.
• Using OLD in an INSERT trigger.
• Using NEW in a DELETE trigger.
• Creating unnecessary triggers for simple application logic.
• Creating triggers that cause unexpected side effects.
• Forgetting that triggers execute automatically.
• Not documenting trigger behavior.
• Making complex trigger chains that are difficult to debug.

Triggers can enforce or automate certain database-side operations, but they should be designed carefully.

For example, if an application inserts a student:

INSERT INTO students
(name, email)
VALUES
('Amit Kumar', 'amit@example.com');

The application may not explicitly insert the audit record because the trigger handles that database-side operation automatically.

Create an audit table:

CREATE TABLE student_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    action VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create an INSERT trigger:

DELIMITER //

CREATE TRIGGER after_student_added
AFTER INSERT ON students
FOR EACH ROW
BEGIN

    INSERT INTO student_audit
    (student_id, action)
    VALUES
    (NEW.student_id, 'INSERT');

END //

DELIMITER ;

Now insert a student:

INSERT INTO students
(name, email)
VALUES
('Neha Kumari', 'neha@example.com');

Check the audit table:

SELECT *
FROM student_audit;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100),
    status VARCHAR(20)
);

CREATE TABLE student_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    action VARCHAR(50),
    old_city VARCHAR(100),
    new_city VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- AFTER INSERT trigger
DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN

    INSERT INTO student_audit
    (student_id, action, new_city)
    VALUES
    (NEW.student_id, 'INSERT', NEW.city);

END //

DELIMITER ;

-- AFTER UPDATE trigger
DELIMITER //

CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN

    IF NOT (OLD.city <=> NEW.city) THEN

        INSERT INTO student_audit
        (student_id, action, old_city, new_city)
        VALUES
        (NEW.student_id,
         'CITY UPDATE',
         OLD.city,
         NEW.city);

    END IF;

END //

DELIMITER ;

-- AFTER DELETE trigger
DELIMITER //

CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
BEGIN

    INSERT INTO student_audit
    (student_id, action, old_city)
    VALUES
    (OLD.student_id, 'DELETE', OLD.city);

END //

DELIMITER ;

-- Test INSERT
INSERT INTO students
(name, email, city, status)
VALUES
('Amit Kumar', 'amit@example.com', 'Patna', 'Active');

-- Test UPDATE
UPDATE students
SET city = 'Gaya'
WHERE student_id = 1;

-- Test DELETE
DELETE FROM students
WHERE student_id = 1;

-- View audit records
SELECT *
FROM student_audit;

-- View triggers
SHOW TRIGGERS;

-- View trigger definition
SHOW CREATE TRIGGER after_student_insert;

-- Remove triggers
DROP TRIGGER IF EXISTS after_student_insert;
DROP TRIGGER IF EXISTS after_student_update;
DROP TRIGGER IF EXISTS after_student_delete;

This example demonstrates INSERT, UPDATE, and DELETE triggers, NEW and OLD values, audit logging, SHOW TRIGGERS, SHOW CREATE TRIGGER, and DROP TRIGGER.