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:
- 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.
- 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
- Create a MySQL TRIGGER using CREATE TRIGGER Statement
- How to access values in the trigger's associated table
- MySQL Trigger example
- Types of triggers
- 1. Before Triggers
- Before trigger example
- 2. After Triggers
- 3. Instead of Triggers
- Drop MySQL trigger
- MySQL DROP TRIGGER example
- INFORMATION_SCHEMA TRIGGERS Table.
- MySQL INFORMATION_SCHEMA TRIGGERS Table query example:
- Conclusion
- You might also like:
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:
- 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.
: refers to a column of an existing row before it is updated or deleted. col_name
NEW.
: refers to the column of a new row to be inserted or an existing row after it is updated.col_name
We generally use the OLD.
, and col_name
NEW.
values from the table for comparison purposes and the availability of these two values depends on the trigger event used:col_name
Event | OLD | NEW | Note |
INSERT | Not available | Available | OLD. value does not exist for an insert statement because there is no value exists in its place before. |
UPDATE | Available | Available | When updating a table row ,the OLD. is the value we want to modify and the NEW. is the new data. |
DELETE | Available | Not available | when deleting a row, the OLD. is the removed value and the NEW. does not exist because we removed the row with its values from the table. |
OLD.col_name
and NEW.col_name
availability in trigger bodyMySQL 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 | Action time |
Before Insert | It is activated before the insertion of data into the table. |
Before Update | It 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 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 trigger | Action time |
After Insert | It is activated after the insertion of data into the table. |
After Update | It is activated after the update of the data in the table. |
After Delete | It is activated after the deletion of data from the table. |
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 trigger | Description |
TRIGGER_CATALOG | The name of the catalog to which the trigger belongs. This value is always def . |
TRIGGER_SCHEMA | The name of the database to which the trigger belongs. |
TRIGGER_NAME | The name of the trigger. |
EVENT_MANIPULATION | The trigger event. This is the type of operation on the associated table for which the trigger activates. The value can be INSERT , DELETE , or UPDATE . |
EVENT_OBJECT_CATALOG , EVENT_OBJECT_SCHEMA , and EVENT_OBJECT_TABLE | Every 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_ORDER | The 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_CONDITION | This value is always NULL . |
ACTION_STATEMENT | The statement executed in the trigger body when the trigger activates. This text uses UTF-8 encoding. |
ACTION_ORIENTATION | This value is always ROW . |
ACTION_TIMING | Whether the trigger activates before or after the triggering event. The value is BEFORE or AFTER . |
ACTION_REFERENCE_OLD_TABLE | This value is always NULL . |
ACTION_REFERENCE_NEW_TABLE | This value is always NULL . |
ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW | The 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 . |
CREATED | The 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_MODE | Is the SQL mode in effect when the trigger was created, and under which the trigger executes. |
DEFINER | The account named in the DEFINER clause is often the user who created the trigger, and it is in ' format. |
CHARACTER_SET_CLIENT | The session value of the character_set_client system variable when the trigger was created. |
COLLATION_CONNECTION | The session value of the collation_connection system variable when the trigger was created. |
DATABASE_COLLATION | The collation of the database with which the trigger is associated. |
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?.
I couldn’t resist commenting. Well written!
What’s up to all, it’s actually a fastidious for me to go to see this web site, it includes valuable Information.