How to Create a Trigger in MySQL

08/06/2021

Contents

In this article, you will learn how to create a trigger in MySQL.

Creating a trigger in MySQL

In MySQL, a trigger is a database object that is associated with a table and automatically executes in response to certain events, such as INSERT, UPDATE, or DELETE operations. Triggers can be used to enforce business rules, maintain referential integrity, or audit database changes.

Syntax

The basic syntax of creating a trigger in MySQL is as follows:

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

The trigger_name is a user-defined identifier that represents the trigger, the table_name is the name of the table that the trigger is associated with, and the {BEFORE | AFTER} {INSERT | UPDATE | DELETE} specifies the event that triggers the execution of the trigger. The FOR EACH ROW clause indicates that the trigger is executed once for each row affected by the event. The trigger body contains the SQL statements that are executed when the trigger is fired.

Examples

Creating a trigger to audit INSERT operations

In this example, we will create a trigger that audits INSERT operations on a table by inserting a row into an audit table.

CREATE TRIGGER audit_insert
    AFTER INSERT ON table_name
    FOR EACH ROW
    BEGIN
        INSERT INTO audit_table (table_name, operation, data)
        VALUES ('table_name', 'INSERT', CONCAT('id: ', NEW.id, ', name: ', NEW.name));
    END;

This trigger is created to audit INSERT operations on a table named table_name. The trigger fires AFTER each INSERT operation, and for each row inserted, it inserts a new row into an audit_table containing the table name, the operation (INSERT), and the data inserted.

Creating a trigger to enforce business rules

In this example, we will create a trigger that enforces a business rule by preventing a row from being inserted if a certain condition is not met.

CREATE TRIGGER check_salary
    BEFORE INSERT ON employee
    FOR EACH ROW
    BEGIN
        IF NEW.salary < 1000 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be less than 1000';
        END IF;
    END;

This trigger is created to enforce a business rule that states that the salary of an employee cannot be less than 1000. The trigger fires BEFORE each INSERT operation, and for each row inserted, it checks if the salary is less than 1000. If the condition is not met, the trigger raises an exception with a custom error message.

Creating a trigger to maintain referential integrity

In this example, we will create a trigger that maintains referential integrity by deleting all child rows when a parent row is deleted.

CREATE TRIGGER delete_child
    BEFORE DELETE ON parent_table
    FOR EACH ROW
    BEGIN
        DELETE FROM child_table WHERE parent_id = OLD.id;
    END;

This trigger is created to maintain referential integrity between a parent table named parent_table and a child table named child_table. The trigger fires BEFORE each DELETE operation on parent_table, and for each row deleted, it deletes all child rows that have a foreign key reference to the deleted parent row.

#MySQL