Automating Your MySQL Database with Triggers

In MySQL(open-source relational database management system), triggers are an essential feature that allows you to execute a series of instructions automatically in response and reaction to specific events, including the insertion, update, or deletion of data (rows) in a database’s table. For instance, a trigger can be used to check values before being inserted into a table or to perform calculations on values involved in an update.

Generally, a trigger is a named database object associated with a specific table and stored in the same database. It activates automatically when a particular associated event occurs for the table without requiring manual human interaction (intervention). This article presents an overview of triggers in MySQL and shows how to use them to automate tasks and simplify your MySQL database management.

Related Articles:

• Best practices for optimizing MySQL database performance.
• How to use ON DELETE CASCADE in MySQL.
• MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
• Using MySQL Event Scheduler: A Complete Guide.
• How to implement One-To-One association in MySQL/Node.js API using async/await.
• How to implement One-To-Many association in MySQL/Node.js API.
• Complete JWT Authentication and Authorization System for MySQL/Node.js API.
• How to interact with MySQL database using async/await promises in node.js ?
• How to create MySQL database using node.js.
• Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.
• How to store Session in MySQL Database using express-mysql-session.

Grant TRIGGER Privilege in MySQL

The TRIGGER privilege enables trigger operations and controls the creation, deletion, execution and display triggers for a table. A trigger can be created and activated only by a user with the TRIGGER privilege and the privilege to execute INSERT, UPDATE, or DELETE statements for the table associated with the trigger.

In MySQL, you can grant the TRIGGER privilege to a specific user by using the GRANT statement as follows:

1. The coming statement gives the TRIGGER privilege for the schema named myDatabaseName to the user userAccount.

 GRANT TRIGGER ON myDatabaseName.* TO 'userAccount'@'host';

2. To give the userAccount user the TRIGGER privilege on all Schemas, use the following statement:

 GRANT TRIGGER ON *.* TO userAccount;

3. Note that the user must have the CREATE privilege on the database where the trigger will be created. If the user doesn’t have the CREATE privilege, they will not be able to create triggers, even if they have the TRIGGER privilege.

Create a MySQL TRIGGER using CREATE TRIGGER Statement

Before we start creating and using triggers, it is important to know that there are a few limitations to triggers in MySQL:

• Triggers are not nested, meaning that a trigger cannot trigger another trigger.
•  Triggers can only be associated with a single table.
•  Triggers cannot be used to update the same table they are associated with.

MySQL triggers are created using the CREATE TRIGGER statement. This statement requires defining the trigger’s name, the specific table associated with it, the type of trigger (before or after insert, update, or delete), and the actions (SQL statement ) to be taken when the trigger is activated.

 CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    [trigger_order]
    trigger_body

Important

When using the BEGIN…END statement constructs in the trigger_body; it is necessary to redefine the MySQL statement delimiter so that you can use the “; ” statement delimiter within the trigger definition.

 
DELIMITER $$
 CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON table_name FOR EACH ROW
    [trigger_order]
    BEGIN
    ... 
   END$$
DELIMITER ;

How to access values in the trigger’s associated table

In the trigger body, you can refer to columns in the table associated with the trigger by using the aliases OLD and NEW. 
OLD.col_name: refers to a column of an existing row before it is updated or deleted. 
NEW.col_name:  refers to the column of a new row to be inserted or an existing row after it is updated.

We generally use the OLD.col_name, and NEW.col_name values from the table for comparison purposes and the availability of these two values depends on the trigger event used:

MySQL Trigger example

In the following example, we assume that we have a table called “Employees” containing data about business workers. And we have a different table called “employeesCount”, and we use it to keep track of the overall number of employees in the business.

 CREATE TABLE Employees ( 
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        position varchar(255) NOT NULL,
        wage INT NOT NULL,
        age INT NOT NULL
        PRIMARY KEY (id));

 CREATE TABLE employeesCount ( 
        count INT(11) NOT NULL,
        average_wage INT NOT NULL,
        capacity INT NOT NULL
        );

To do this, we create an after trigger on the “Employees” table that, each time a new employee is added to the “Employees” table, updates the “employeesCount” table.

 DELIMITER $$
CREATE TRIGGER update_employee_count
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    UPDATE employeesCount
    SET count = count + 1;
END$$
DELIMITER ;

Now, every time a new employee is inserted into the “Employees” table, the “update_employee_count” trigger will automatically update the “employeesCount” table to reflect the change. This helps to ensure that the information in both tables is constantly updated and accurate.

Types of triggers

As we already said, a trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated, or it can be used to replace the event that occurs.

There are three types of triggers in MySQL:

1. Before Triggers

This trigger is executed before the event occurs and can be used to validate data before it is inserted, updated, or deleted.

Before trigger example

In this example, the trigger “employee_age” is associated with a “BEFORE INSERT” operation on the previous “Employees” table. The trigger checks the “age” attribute of the new record is inserted, and if the value is less than 18, the trigger raises an error with the message “Employee must be older than 18.”

delimiter $$
CREATE TRIGGER employee_age
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Employee must be older than 18.';
    END IF;
END $$
delimiter ;

2. After Triggers

This trigger is executed after the event occurs and can be used to update other tables or perform other actions based on the data that has been changed. The example illustrated in the MySQL trigger example section in this article is an excellent example of an after insert trigger.

3. Instead of Triggers

An “INSTEAD OF” trigger enables you to change a table’s data before it is added, modified, or removed. When you want to build custom logic for data alteration or when you want to stop specific sorts of data modification from happening, this kind of trigger might be helpful. For instance, an “INSTEAD OF” trigger may be used to stop duplicate data from being added to a database. If the data being put is already present in the table, the trigger might determine this and stop the insert activity.
In the following example, the trigger is named “trigger_name”. It is an “INSTEAD OF INSERT” trigger, and it is associated with the “table_name” table. The trigger action statements define the actions to be taken when the trigger is activated, which occur instead of the actual insert operation.

DELIMITER $$
CREATE TRIGGER trigger_name
INSTEAD OF INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger action statements
END$$
DELIMITER ;

Drop MySQL trigger

We use the DROP TRIGGER statement in MySQL to drop a trigger and here is its the basic syntax :

 
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

• The schema’s name (database) is optional, meaning if the schema is omitted, the trigger is dropped from the default schema.
•  To delete a trigger using the DROP TRIGGER statement, the user must have TRIGGER privilege for the table associated with the trigger.
•  To prevent an error from occurring for a trigger that does not exist, we use the IF EXISTS.
•  When using IF EXISTS, a NOTE is produced for a trigger that doesn’t exist.
•  When a table is dropped, its triggers are dropped automatically by MySQL.

MySQL DROP TRIGGER example

In this example, we will drop the trigger “update_employee_count” that we created in the example above. This trigger is associated with the “Employees” table.

 CREATE TABLE Employees ( 
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        position varchar(255) NOT NULL,
        wage INT NOT NULL,
        age INT NOT NULL
        PRIMARY KEY (id));

 DELIMITER $$
CREATE TRIGGER update_employee_count
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    UPDATE employeesCount
    SET count = count + 1;
END$$
DELIMITER ;

So to drop the trigger, we use the DROP TRIGGER statement as following:

 
DROP TRIGGER IF EXISTS  update_employee_count;

To verify if the trigger is removed, use the SHOW TRIGGERS:

 
 SHOW TRIGGERS;

INFORMATION_SCHEMA TRIGGERS Table.

As we already said, a trigger is a named database object associated with a table, and to obtain metadata about triggers, we query the INFORMATION_SCHEMA TRIGGERS Table. But you have to remember that the TRIGGERS table provides information about triggers. So to see this information, you must have the TRIGGER privilege for the table.

The following table resumes all the information and data we can get from the TRIGGERS table:

MySQL  INFORMATION_SCHEMA TRIGGERS Table query example:

The following example uses the “update_employee_count” trigger created in the previous sections of this article.

 SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='schema_name' AND TRIGGER_NAME='update_employee_count';

Conclusion

In conclusion, triggers are a crucial tool in MySQL that lets you automate tasks and simplify database management. To efficiently utilize triggers in your MySQL database, you need to comprehend the different sorts of triggers, how to create a trigger and the limitations of triggers.

You might also like:

Best practices for optimizing MySQL database performance.

How to use ON DELETE CASCADE in MySQL.

MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.

Using MySQL Event Scheduler: A Complete Guide.

Complete JWT Authentication and Authorization System for MySQL/Node.js API.

How to Build a Complete API for User Login and Authentication using MySQL and Node.js.

Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.

Nodemailer + Gmail: How to Send Emails from Node.js API using Gmail.

How to store Session in MySQL Database using express-mysql-session.

How to interact with MySQL database using async/await promises in node.js ?

How to use Sequelize async/await to interact with MySQL database in Node.js.

MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.

ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js

ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.

How to add Routes to insert data into MySQL database-related tables in Node.js API?

Example How to use initialize() Function in Node.js/Express API .

Why is Connection Pooling better than Single Connection?.

How to create MySQL database using node.js.

How to Get Current Date and Time in JavaScript.