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:

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 INSERTUPDATE, 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

ClausesDescription and Values
userSpecifies the MySQL account to be used when checking access privileges at trigger activation time. 
If the DEFINER clause is omitted, the default definer is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
trigger_name
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
trigger_timeIs the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.
trigger_time: { BEFORE | AFTER }
trigger_eventIndicates the kind of operation that activates the trigger. These trigger_event values are :
trigger_event: { INSERT | UPDATE | DELETE }
INSERT: The trigger activates whenever a new row is inserted into the table.
UPDATE: The trigger activates whenever a row is modified.
DELETE: The trigger activates whenever a row is deleted from the table. 
Note that: 
DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger because they do not use DELETE. 
Dropping a partition does not activate DELETE triggers, either.
table_nametable_name is the name of the table which the trigger is associated with. This table must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.
trigger_orderWhen we define multiple triggers for a given table with the same trigger event and action time, by default, triggers that have the same trigger event and action time activate in the order they were created. To change trigger order, we specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger with the same trigger event and action time.
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name.
FOLLOWS:the new trigger activates after the existing trigger.
PRECEDES: the new trigger activates before the existing trigger.
trigger_bodyIt contains the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN … END compound statement construct.
MySQL Trigger Syntax

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:

Event OLDNEWNote
INSERTNot availableAvailableOLD.col_name value does not exist for an insert statement because there is no value exists in its place before.
UPDATEAvailableAvailableWhen updating a table row ,the OLD.col_name is the value we want to modify and the NEW.col_name is the new data.
DELETEAvailableNot availablewhen deleting a row, the OLD.col_name is the removed value and the NEW.col_name does not exist because we removed the row with its values from the table.
OLD.col_name and NEW.col_name availability in trigger body

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 triggerAction time
Before InsertIt is activated before the insertion of data into the table.
Before UpdateIt is activated before the update of data in the table.
Before Delete It is activated before the data is removed from the table.
Before trigger and action time

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.

After triggerAction time
After InsertIt is activated after the insertion of data into the table.
After UpdateIt is activated after the update of the data in the table.
After Delete It is activated after the deletion of data from the table.
After trigger and action time

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:

Information about triggerDescription
TRIGGER_CATALOGThe name of the catalog to which the trigger belongs. This value is always def.
TRIGGER_SCHEMAThe name of the database to which the trigger belongs.
TRIGGER_NAMEThe name of the trigger.
EVENT_MANIPULATIONThe trigger event. This is the type of operation on the associated table for which the trigger activates. The value can be INSERTDELETE, or UPDATE.
EVENT_OBJECT_CATALOGEVENT_OBJECT_SCHEMA, and EVENT_OBJECT_TABLEEvery trigger is associated with exactly one table. These columns indicate the catalog and database in which this table occurs, and the table name, respectively. The EVENT_OBJECT_CATALOG value is always def.
ACTION_ORDERThe position of the trigger’s action within the list of triggers on the same table with the same EVENT_MANIPULATION and ACTION_TIMING values.
ACTION_CONDITIONThis value is always NULL.
ACTION_STATEMENTThe statement executed in the trigger body when the trigger activates. This text uses UTF-8 encoding.
ACTION_ORIENTATIONThis value is always ROW.
ACTION_TIMINGWhether the trigger activates before or after the triggering event. The value is BEFORE or AFTER.
ACTION_REFERENCE_OLD_TABLEThis value is always NULL.
ACTION_REFERENCE_NEW_TABLEThis value is always NULL.
ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROWThe old and new column identifiers, respectively. The ACTION_REFERENCE_OLD_ROW value is always OLD and the ACTION_REFERENCE_NEW_ROW value is always NEW.
CREATEDThe date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers created in MySQL 5.7.2 or later, NULL for triggers created prior to 5.7.2.
SQL_MODEIs the SQL mode in effect when the trigger was created, and under which the trigger executes.
DEFINERThe account named in the DEFINER clause is often the user who created the trigger, and it is in 'user_name'@'host_name' format.
CHARACTER_SET_CLIENTThe session value of the character_set_client system variable when the trigger was created.
COLLATION_CONNECTIONThe session value of the collation_connection system variable when the trigger was created.
DATABASE_COLLATIONThe collation of the database with which the trigger is associated.
MySQL  INFORMATION_SCHEMA 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:

Password Hashing in MySQL 8.0: Authentication Plugins

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.

2 thoughts on “Automating Your MySQL Database with Triggers”

Leave a Comment

Your email address will not be published. Required fields are marked *

Translate »