MySQL Create Trigger

Summary: in this tutorial, you will learn how to use the MySQL CREATE TRIGGER statement to create a trigger associated with a table.

Introduction to MySQL CREATE TRIGGER statement

A trigger is a set of SQL statements, that is executed automatically in response to a specified event including INSERT, UPDATE, or DELETE on a particular table.

The CREATE TRIGGER statement allows you to create a new trigger associated with a table.

Here’s the syntax of the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body (SQL statements)
END;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• trigger_name: Name of the trigger.
• BEFORE or AFTER: Specifies when the trigger should be executed.
• INSERT, UPDATE, or DELETE: Specifies the type of operation that activates the trigger.
• table_name: Name of the table on which the trigger is defined.
• FOR EACH ROW: Indicates that the trigger should be executed once for each row affected by the triggering event.
• BEGIN and END: Delimit the trigger body, where you define the SQL statements to be executed.

The trigger body can access the values of the column being affected by the operation.

To distinguish between the value of the columns BEFORE and AFTER the event has fired, you use the NEW and OLD modifiers.

For example, if you update the value in the description column, in the trigger body, you can access the value of the description column before the update OLD.description and the new value NEW.description.

The following table illustrates the availability of the OLD and NEW modifiers:

MySQL trigger example

First, create a new table called items:

CREATE TABLE items (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);
Code language: PHP (php)

Second, insert a row into the items table:

INSERT INTO items(id, name, price) 
VALUES (1, 'Item', 50.00);Code language: JavaScript (javascript)

Third, create the item_changes table to store the changes made to the data in the items table:

CREATE TABLE item_changes (
    change_id INT PRIMARY KEY AUTO_INCREMENT,
    item_id INT,
    change_type VARCHAR(10),
    change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id)
);Code language: PHP (php)

Fourth, create a trigger called update_items_trigger associated with the items table:

DELIMITER //

CREATE TRIGGER update_items_trigger
AFTER UPDATE
ON items
FOR EACH ROW
BEGIN
    INSERT INTO item_changes (item_id, change_type)
    VALUES (NEW.id, 'UPDATE');
END;
//

DELIMITER ;Code language: PHP (php)

In this example:

• The trigger is named update_items_trigger.
• It is set to execute AFTER UPDATE on the items table.
• The trigger body inserts a record into the item_changes table with the item_id and change_type.

Now, whenever you update a row in the items table, the trigger will run to add the corresponding record to the item_changes table.

Fifth, update a row in the items table:

UPDATE items
SET price = 60.00 
WHERE id = 1;

Finally, retrieve data from the item_changes table to see the logged changes:

SELECT * FROM item_changes;

Output:

+-----------+---------+-------------+---------------------+
| change_id | item_id | change_type | change_timestamp    |
+-----------+---------+-------------+---------------------+
|         1 |       1 | UPDATE      | 2023-12-27 18:21:43 |
+-----------+---------+-------------+---------------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

Summary

• Use the MySQL CREATE TRIGGER statement to create a new trigger in the database.