In this blog, you will study how to create several triggers for the identical event and action time in MySQL.
Earlier MySQL version 5.7.2, you can first make one trigger for an event in a table e.g., you can first make one trigger for the BEFORE UPDATE or AFTER UPDATE event. MySQL 5.7.2+ kicks this restraint and lets you to create multiple triggers for the similar event and action time in a table. The triggers will start serially when the event happens.
The syntax for making the primary trigger remains the same. In case you have several triggers for the similar event in a table, MySQL will raise the triggers in the order that they were formed. To modify the direction of triggers, you need to require FOLLOWS or PRECEDES after the FOR EACH ROW section.
The FOLLOWS option lets the new trigger to start after the present trigger.
The PRECEDES option let’s the new trigger to start before the present trigger.
The subsequent is the syntax of making a new extra trigger with clear order:
MySQL Multiple Triggers Case
Let’s look at an instance of making several triggers on the same event and action time in a table to get a improved thoughtful.
We will practice the products table in the example database for the demo. What if, every time we change the price of a product (column MSRP), we need to log the old price in a distinct table named price_logs .
Primary, we make a new price_logs table by means of the CREATE TABLE declaration as follows:
Next, we create a new trigger that starts when the BEFORE UPDATE event of the products table happens. The trigger’s term is before_products_update:
Third, we modify the price of a product and query the price_logs table by means of the following UPDATE query:
It works as anticipated.
Assume we want to get not only the old price and when it was altered but also who altered it. We can add extra columns to the price_logs table. But, for the resolve of multiple triggers demo, we will create a new table to store the data of users who completed the changes. The name of the new table is user_change_logs with the subsequent structure:
Here and now, we create another trigger that starts on the BEFORE UPDATE event of the products table. This trigger will update the user_change_logs table with the data of the user who completed the changes. It is started after the before_products_update trigger.
Let’s do a swift test.
Primary, we update the prices of the product by means of the UPDATE statement as follows:
Next, we request the data from together price_logs and user_change_logs tables:
Statistics on Triggers Order
If you use the SHOW TRIGGERS declaration, you will not get the order that triggers start for the same event and action timing in a table.
To find this info, you need to request the action_order column in the triggers table of the information_schema database as follows:
In this blog, we have revealed you how to create several triggers on the similar event for a table in MySQL.