Maximum performance at minimal cost. Start your server in seconds now!

    How To Use Triggers in MySQL

    Introduction

    Most data operations, including Triggers in MySQL, are performed through explicitly executed SQL queries like SELECT, INSERT, or UPDATE.

    However, SQL databases can also be instructed to perform pre-defined actions automatically every time a specific event occurs through triggers.

    For instance, you can use Triggers in MySQL to keep an audit trail log of all DELETE statements, or to automatically update aggregated statistical summaries whenever rows are updated or appended to the table.

    In this tutorial, you’ll be using different Triggers in MySQL to automatically perform actions whenever rows are inserted, updated, or deleted.

    Prerequisites for Triggers in MySQL

    To follow this guide, you’ll need a computer running a SQL-based relational database management system (RDBMS). This guide’s instructions and examples are validated for the following setup:

    • A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as detailed in our initial setup guide for Ubuntu 20.04.
    • MySQL installed and secured, using a non-root MySQL user as described in Step 3 of the How To Install MySQL on Ubuntu 20.04 guide.
    • Basic familiarity with executing SQL queries such as SELECT, INSERT, UPDATE, and DELETE, following our guides on these commands.

    It’s also helpful to know nested queries and aggregate functions, as covered in our guides on How To Use Nested Queries in SQL and How To Use Mathematical Expressions and Aggregate Functions in SQL.

    Note: Although many RDBMSs have unique SQL implementations, Triggers in MySQL are part of the SQL standard. However, syntax and behavior may differ across databases, so the commands here follow MySQL syntax and might not work identically on other systems.

    Additionally, you’ll need a database with sample data tables for practice. Therefore, please refer to the Connecting to MySQL and Setting up a Sample Database section to set up a test database used in this guide’s examples.

    Connecting to MySQL and Setting up a Sample Database

    In this section, you will connect to a MySQL server and create a sample database to follow upcoming examples.

    Therefore for this guide, you’ll use an imaginary collectibles collection. You’ll store details about currently owned collectibles, keep their total worth readily available, and ensure that removing a collectible always leaves a trace.

    If your SQL database system runs on a remote server, SSH into your server from your local machine:

    Copy Code 
    

    ssh sammy@your_server_ip

    Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

    Copy Code 
    

    mysql -u sammy -p

    Create a database named collectibles:

    Copy Code 
    

    CREATE DATABASE collectibles;

    If the database was created successfully, you’ll receive output like this:

    Output
Query OK, 1 row affected (0.01 sec)

    To select the collectibles database, run the following USE statement:

    Copy Code 
    

    USE collectibles;

    You will receive the following output:

    Copy Code 
    

    Output
Database changed

    After selecting the database, you can create sample tables within it to practice using Triggers in MySQL. The table collectibles will contain simplified data about collectibles in the database. It will hold the following columns:

    • name: This column holds the name for each collectible, expressed using the varchar data type with a maximum of 50 characters.
    • value: This column stores the collectible’s market value using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

    Create the sample table with the following command:

    Copy Code 
    

    CREATE TABLE collectibles (
    name varchar(50),
    value decimal(5, 2)
);

    If the following output prints, the table has been created:

    Copy Code 
    

    Output
Query OK, 0 rows affected (0.00 sec)

    In addition the next table will be called collectibles_stats and will be used to keep track of the accumulated worth of all the collectibles in the collection. It will hold a single row of data with the following columns:

    • count: This column holds the number of owned collectibles, expressed using the int data type.
    • value: This column stores the accumulated worth of all collectibles using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.

    Create the sample table with the following command:

    Copy Code 
    

    CREATE TABLE collectibles_stats (
    count int,
    value decimal(5, 2)
);

    If the following output prints, the table has been created:

    Copy Code 
    

    Output
Query OK, 0 rows affected (0.00 sec)

    Nevertheless the third table, collectibles_archive, tracks all removed collectibles to ensure they are never lost. It will hold data similar to the collectibles table, augmented with the removal date. It uses the following columns:

    • name: This column holds the name for each removed collectible, expressed using the varchar data type with a maximum of 50 characters.
    • value: This column stores the collectible’s market value at the moment of deletion using the decimal data type with a maximum of 5 values before the decimal point and 2 values after it.
    • removed_on: This column stores the date and time of deletion for each archived collectible using the timestamp data type with the default value of NOW(), meaning the current date whenever a new row is inserted into this table.

    Create the sample table with the following command:

    Copy Code 
    

    CREATE TABLE collectibles_archive (
    name varchar(50),
    value decimal(5, 2),
    removed_on timestamp DEFAULT CURRENT_TIMESTAMP
);

    If the following output prints, the table has been created:

    Copy Code 
    

    Output
Query OK, 0 rows affected (0.00 sec)

    Following that, load the collectibles_stats table with the initial state for the empty collectibles collection by running the following INSERT INTO operation:

    Copy Code 
    

    INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

    The INSERT INTO operation adds a row to collectibles_stats. It uses aggregate functions to count all rows in collectibles and sums their values with the SUM function. The following output indicates that the row has been added:

    Copy Code 
    

    Output
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

    You can verify that by executing a SELECT statement on the table:

    Copy Code 
    

    SELECT * FROM collectibles_stats;

    Since there are no collectibles in the database yet, the initial number of items is 0 and the accumulated value says NULL:

    Copy Code 
    

    Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

    With that, you’re ready to follow the rest of the guide and begin using triggers in MySQL.

    Understanding Triggers in MySQL

    Triggers are statements for a table, executed automatically by the database whenever a specified event occurs. Triggers ensure consistent actions whenever a specific statement is executed on a table, removing manual execution by users.

    Every trigger associated with a table is identified with a user-defined name and a pair of conditions. These instruct the database engine when to execute the trigger. Those can be grouped into two separate classes:

    • Database event: The trigger can be executed when INSERT, UPDATE, or DELETE statements are run on a table.
    • Event time: Additionally, triggers can be executed BEFORE or AFTER the statement in question.

    Combining the two condition groups yields a total of six separate trigger possibilities. They are executed automatically each time the joint condition is met. The triggers that happen before the statement meeting the condition is executed are BEFORE INSERT, BEFORE UPDATE, and BEFORE DELETE. These can validate and manipulate data before insertion or update, or save deleted row details for auditing.

    …

    Conclusion

    By following this guide, you learned what SQL triggers are and how to use them to manipulate data. You learned to use BEFORE DELETE triggers to archive deleted rows and AFTER triggers to keep summaries updated.

    You can use functions to offload some of the data manipulation and validation to the database engine, ensuring data integrity or hiding some of the database behaviors from the daily database user. This tutorial covered only the basics of using triggers for that purpose. You can build complex triggers consisting of multiple statements and use conditional logic to perform actions even more granularly. To learn more about that, refer to the MySQL documentation on triggers.