Understanding MySQL Triggers: Syntax and Usage Examples

A trigger is a predefined program that executes automatically when an INSERT, UPDATE, or DELETE operation happens in a specified table. For example, you can create a MySQL trigger that executes automatically when a row is deleted.

There are several trigger types like BEFORE UPDATE and AFTER UPDATE triggers, which we will discuss in subsequent sections.

MySQL supports the row-level trigger type. It activates every time a row is inserted, updated, or deleted.

Uses of triggers

Triggers are used for several reasons including the following:

• Data validation: Triggers ensure data meets specified validation rules before inserting or updating a table. For example, you can check if an input is a number.

• Preventing unauthorized changes: They can prevent unauthorized changes by checking if the data meets certain criteria. For instance, a trigger can prevent deleting records in a student's table if they are still taking an exam.

• Automating updates: Triggers can facilitate automatic changes across related tables. When a record is modified in one table, a trigger can automatically update records in related tables, ensuring data integrity and consistency.

• Change logging: Triggers allow you to track and record all INSERT, UPDATE, and DELETE operations. This comes in handy when dubbing or auditing your database.

Using triggers

So, to use MySQL triggers, you must have a database with a table(s). Let’s create one, a student management database.

To create a database, we’ll use the syntax:

CREATE DATABASE student_management;

Now, we can use the student management database:

USE student_management;

Let’s define the table’s schema:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

This creates a students table with the above columns. Let’s populate the table with a few student details.

INSERT INTO students (name, age, email) VALUES 
('Alice Johnson', 19, 'alice.johnson@example.com'),
('Bob Smith', 22, 'bob.smith@example.com'),
('Carol Taylor', 20, 'carol.taylor@example.com'),
('David Lee', 21, 'david.lee@example.com');

Let’s verify that the data has been entered correctly:

SELECT * FROM students;

We get:

To record and track changes in the students' table, we will create a student_logs table. It will record every INSERT, UPDATE, and DELETE operation.

CREATE TABLE student_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    operation VARCHAR(50) NOT NULL,
    student_id INT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

With the tables set, we can create our triggers.

Creating triggers in MySQL

To create a trigger, you use the below syntax:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements to execute when the trigger is fired
END;

• CREATE TRIGGER creates a new trigger.

• trigger_name is the name of the trigger.

• {BEFORE | AFTER} specifies whether the trigger should execute before or after an event.

• {INSERT | UPDATE | DELETE} is the event type that fires.

• table_name is a table where the trigger is attached.

• FOR EACH ROW indicates that the trigger will execute for each affected row.

• BEGIN … END contains the SQL statements that will run when the trigger is fired.

MySQL INSERT triggers

Note: When writing triggers, you often include multiple SQL statements inside a single block (enclosed by BEGIN ... END). If the default delimiter ; is used, MySQL will interpret each statement inside the block as a separate SQL command, causing errors.

To avoid this, you temporarily change the delimiter to something else (e.g., $$ or //) while defining the block. After the block is defined, you reset the delimiter back to the default ;.

BEFORE INSERT trigger

A BEFORE INSERT trigger is used to validate or modify data before it is entered into a table.

Let’s create a trigger that ensures the student’s age is greater than 0.

DELIMITER $$

CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    -- Ensure age is greater than 0
    IF NEW.age <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Age must be greater than 0';
    END IF;
END$$

DELIMITER ;

First, we change the delimiter to $$ , then define our trigger. Next, the before_student_insert checks whether the new student’s age is less than 0. If it is less than 0, then SIGNAL SQLSTATE '45000' generates an error message Age must be greater than 0. We then change our delimiter to ;

Let’s try it.

INSERT INTO students (name, age, email) VALUES ('John Doe', 20, 'john.doe@example.com');

This will be inserted successfully.

INSERT INTO students (name, age, email) VALUES ('Jane Doe', -5, 'jane.doe@example.com');

The trigger raises an error since the age is less than 0.

AFTER INSERT trigger

An AFTER INSERT trigger executes when a new record is successfully added to a table.

DELIMITER $$

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    -- Log the operation in the student_logs table
    INSERT INTO student_logs (operation, student_id, timestamp)
    VALUES ('INSERT', NEW.id, NOW());
END$$

DELIMITER ;

We create after_student_insert that fires after a new row is inserted into the students' table.

The INSERT INTO adds a record to the student_logs table, which includes the operation (INSERT), student_id, and timestamp (when the student was added).

Let’s add a new student:

INSERT INTO students (name, age, email) VALUES ('Jane Doe', 20, 'jane.doe@example.com');

Let’s check the students_log table:

SELECT * FROM student_logs

We get:

MySQL UPDATE triggers

BEFORE UPDATE trigger

Like the BEFORE INSERT trigger, this type fires before an UPDATE operation happens on a table.

For example, we can create a trigger that ensures the age is always greater than 0 and logs an error if you try to update it with an invalid value.

DELIMITER $$

CREATE TRIGGER before_student_update
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
    -- Ensure age is greater than 0
    IF NEW.age <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Age must be greater than 0';
    END IF;
END$$

DELIMITER ;

Let’s update the first student’s age:

UPDATE students SET age = 21 WHERE id = 1;

This will update successfully. However, if we use an invalid value:

UPDATE students SET age = -5 WHERE id = 1;

The trigger throws an error:

AFTER UPDATE trigger

An AFTER UPDATE trigger fires after a successful operation on a table.

DELIMITER $$

CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    -- Log the update operation in the student_logs table
    INSERT INTO student_logs (operation, student_id, old_value, new_value, timestamp)
    VALUES (
        'AFTER_UPDATE', 
        OLD.id, 
        CONCAT('Name: ', OLD.name, ', Age: ', OLD.age), 
        CONCAT('Name: ', NEW.name, ', Age: ', NEW.age), 
        NOW()
    );
END$$

DELIMITER ;

OLD refers to the values before the update while NEW refers to the values after the update.

Make sure to add the old_value and new_value rows, otherwise, you’ll run into an error.

ALTER TABLE student_logs 
ADD COLUMN old_value TEXT, 
ADD COLUMN new_value TEXT;

Let’s update the first student’s record:

UPDATE students SET name = 'Alice Doe', age = 22 WHERE id = 1;

Checking the student_logs table:

SELECT * FROM student_logs;

We get:

MySQL DELETE trigger

As you’d expect, a DELETE trigger fires after a DELETE operation is performed on a table.

DELIMITER $$

CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    -- Log the delete operation in the student_logs table
    INSERT INTO student_logs (operation, student_id, old_value, timestamp)
    VALUES (
        'DELETE', 
        OLD.id, 
        CONCAT('Name: ', OLD.name, ', Age: ', OLD.age), 
        NOW()
    );
END$$

DELIMITER ;

Let’s delete a record from the students table and check the student_logs table:

DELETE FROM students WHERE id = 1;
SELECT * FROM student_logs;

We get:

You can see all triggers using the SHOW TRIGGERS; syntax. You can then delete a trigger using the DROP TRIGGER trigger_name; syntax.

Conclusion

I hope this article has helped you understand how MySQL triggers work. With triggers you can automate actions, log changes, and enforce data integrity.