Introduction
For a long time MySQL server supported only one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). In other words, there could be at most one trigger for every pair (action, timing). It means that a user couldn’t assign for example two BEFORE INSERT triggers for the same table t1. To workaround this restriction and allow several actions to fire on some table event, a user had to implement several stored procedures (one for each activity that would be implemented as independent trigger), create trigger for a table and call this stored procedures from the trigger. As of MySQL 5.7.2 this limitation has been removed. It means that starting the MySQL 5.7.2 a user can create for example, two BEFORE INSERT triggers, three AFTER INSERT triggers and four BEFORE UPDATE triggers for table t1. And this triggers will be called in the prescribed order determined (in generally) by the sequence in which triggers were created.
Implementation overview
In the implementation of multiple triggers per table the main point was the order of triggers execution. In according with SQL-standard 2011 the order of execution of a set of triggers is “ascending by value of their timestamp of creation in their descriptors, such that the oldest trigger executes first. If one or more triggers have the same timestamp value, then their relative order of execution is implementation-defined”. To follow the standard it was introduced new column ‘ACTION_ORDER’ of INFORMATION_SCHEMA.TRIGGERS that takes the ordinal activation value. Also the attribute ‘CREATED’ of INFORMATION_SCHEMA.TRIGGERS that was supported by server before 5.7.2 and was always NULL now stores the trigger creation timestamp. The value of the attribute CREATED is set at the trigger creation time and can not be changed afterwards (the only way to change it is to drop and re-create the trigger).
Those two attributes have the following characteristics:
- Both are read-only;
- CREATED is a persistent attribute, stored in the data dictionary;
- ACTION_ORDER is not a persistent attribute for the current data dictionary. Its value is calculated when table’s triggers are loaded and defined by the ordinal position of trigger definition inside the attribute ‘triggers’ of .TRG file;
- The attribute CREATED is shown in the result set of statements SHOW TRIGGERS and SHOW CERATE TRIGGER;
- The attribute ACTION_ORDER isn’t shown in the result set of statement SHOW TRIGGERS and SHOW CREATE TRIGGERS;
- For both attributes their values might be queried from the table INFORMATION_SCHEMA.TRIGGERS;
- The attribute CREATED is displayed in the session timezone;
- The attribute CREATED equals to NULL for triggers created before MySQL server version 5.7.2;
- It is possible to create triggers with the same CREATED values;
- The attribute ACTION_ORDER is a positive integer.
During table opening the server loads triggers assigned to the table from the data dictionary into the main memory. Triggers are loaded in the order defined by the field ‘triggers’ in the file ‘.TRG’. For every trigger being loaded the server assigns the value to ACTION_ORDER attribute that is one greater than the ACTION_ORDER value of trigger loaded before (if any). ACTION_ORDER numeration starts from 1. Later when a user creates a new trigger issuing the statement ‘CERATE TRIGGER …’ the server assigns the value to attribute ACTION_ORDER based on the value of this attribute already assigned to table triggers for the same action/timing pair. For example, lets consider the table t1 and suppose that there are two triggers for this table created by server before 5.7.2. Lets say, that those two triggers are:
- trigger t1_bi, BEFORE INSERT ON t1
- trigger t1_bu, BEFORE UPDATE ON t1
Then after MySQL server 5.7.2 was started and table t1 was loaded into the main memory we have:
mysql> SELECT trigger_name, action_order FROM information_schema.triggers WHERE information_schema.triggers.event_object_table=’t1′;
+——————–+———————+
| trigger_name | action_order |
+——————–+———————+
| t1_bi | 1 |
| t1_bu | 1 |
+——————–+———————+
Then if a user creates another trigger for the pair action/timing for that a trigger already exists (for example, another one BEFORE INSERT trigger), then the server will assign the value of attribute ACTION_ORDER that is greater by one than previously assigned ACTION_ORDER value for this pair action/timing. That is, if user executes the following statements:
- ‘CREATE TRIGGER t1_2_bi BEFORE INSERT ON t1 …’
- ‘CREATE TRIGGER t1_3_bi BEFORE INSERT ON t1 …’
- ‘CREATE TRIGGER t1_2_bu BEFORE UPDATE ON t1 …’
then the query SELECT trigger_name, action_order FROM information_schema.triggers WHERE information_schema.triggers.event_object_table=’t1′ will have the following result set:
+——————–+———————+
| trigger_name | action_order |
+——————–+———————+
| t1_bi | 1 |
| t1_2_bi | 2 |
| t1_3_bi | 3 |
| t1_bu | 1 |
| t1_2_bu | 2 |
+——————–+———————+
As you can seen the numeration of attribute ACTION_ORDER is independent for every pair action/timing.
ACTION_ORDER values are used by server to control the order of trigger execution. Server executes triggers in the ascending order of ACTION_ORDER values. Since ACTON_ORDER is implicitly calculated by the server, it implies a number of important consequences:
- Triggers has to be dumped in the ascending order of ACTION_ORDER attribute. This order ensures that during the restore triggers will be re-created in the same order;
- Replication should preserve ACTION_ORDER and CREATED attributes. Since CREATE TRIGGER statements are logged in the order user entered them, then ACTION_ORDER is preserved.
Attribute CREATED is pure informational attribute. Its values isn’t take into account during triggers execution. mysqldump doesn’t preserve trigger creation timestamps. Replication preserves CREATED attributes.
MySQL Server supports extension to SQL-Standard that allows to set trigger execution order based on position of new trigger against other table triggers. Namely, it was introduced the following clauses from the statement CREATE TRIGGER:
- FOLLOWS <trigger name>
- PRECEDES <trigger name>
Those clauses allows to set explicit triggers execution order based on location of trigger being inserted relative to other triggers:
- FOLLOWS specifies the name of the existing trigger, after which the trigger being created should be activated;
- PRECEDES specifies the name of the existing trigger, before which the trigger being created should be activated;
- If FOLLOWS or PRECEDES specifies the name of non-existing trigger, an error (an SQL condition of error level) is thrown (ER_TRG_DOES_NOT_EXIST), and the CREATE TRIGGER statement fails;
- FOLLOWS/PRECEDES will not appear in any auto-generated SQL;
- FOLLOWS/PRECEDES are optional.
Lets consider the example. There is a table t1 and a user creates the following triggers for this table:
- CREATE TRIGGER t1_bi_1 BEFORE INSERT ON t1 FOR EACH ROW ….
- CREATE TRIGGER t1_bi_2 BEFORE INSERT ON t1 FOR EACH ROW ….
- CREATE TRIGGER t1_bi_1_1 BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS t1_bi ….
- CREATE TRIGGER t1_bi_0_1 BEFORE INSERT ON t1 FOR EACH ROW PRECEDES t1_bi ….
Then the query SELECT trigger_name, action_order FROM information_schema.triggers WHERE information_schema.triggers.event_object_table=’t1′ will return the following result set:
+——————–+———————+
| trigger_name | action_order |
+——————–+———————+
| t0_bi_0_1 | 1 |
| t1_bi | 2 |
| t1_bi_1 | 3 |
| t1_bi_2 | 4 |
+——————–+———————+
and during execution of statement ‘INSERT INTO t1 …” triggers will be executed in the order t0_bi_0_1, t1_bi, t1_bi_1, t1_bi_2.
Changes to data dictionary
Currently triggers are stored in TRG (TRN) files. Main definitions are stored in the TRG-file (format: <table name>.TRG). This is a plain text file, having the following structure:
TYPE=TRIGGERS
triggers=’trigger1-definition’ ‘trigger2-definition’
sql_modes=sqlmode1 sqlmode2
definers=definer1 definer2
…
To support persistent storage of creation timestamp for triggers the new attribute ‘created’ was added to the TRG file. This attribute contains a list of timestamps for every trigger assigned to the table.
Changes to error messages
The MySQL server before 5.7.2 threw ER_NOT_SUPPORTED_YET (1235 / 42000) if the user attempted to create more than one trigger with the same action and event for one table:
ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘multiple
triggers with the same action time and event for one table’
As of 5.7.2 this message isn’t appear any longer. The error message itself (ER_NOT_SUPPORTED_YET) will of course remain.
Code test coverage
21 new MTR tests were added during implementation of new functionality. These test are public and reside in mysql-test/t/trigger_wl3253.test and mysql-test/suite//rpl/t/rpl_trigger.test. Implemented changes were covered for 89.7% by these tests.
Technical details
Implementation of triggers support was significantly refactored during adding support for multiple triggers per table for the same action/timing. The figure below depicts the major classes involved in support of triggers in MySQL server.
Fig. 1. Major classes for support triggers in MySQL Server
Main activities related to triggers can be categorized as loading triggers from Data Dictionary, handling statement CREATE TRIGGER and executing triggers assigned to a table.
Lets describe in short every of this activities.
Loading triggers from the Data Dictionary
- On the last step of table opening process an instance of class Table_trigger_dspatcher is created and assigned to an object TABLE
- The method Table_trigger_dspatcher::check_n_load is called
- Method Table_trigger_dspatcher::check_n_load calls the method Trigger_loader::load_triggers to load triggers assigned to the table from the Data Dictionary to the memory
- For every unique pair action/timing for that triggers were loaded from Data Dictionary an instance of Trigger_chain is created.
- The method Trigger_chain::add_trigger is called for every trigger with the same action/timing value.
Handling of statement CREATE TRIGGER
- Check whether an object of TABLE has initialized pointer to a instance of Table_trigger_dispatcher. Create instance of Table_trigger)dispatcher if it is not.
- The method Table_trigger_dispatcher::create is called that does the following steps
- Create new object of Trigger class
- Check whether there is an instance of class Trigger_chain for event/action time specified in the statement CREATE TRIGGER. Create new instance of class Trigger_chain if not.
- Call Trigger_chain::add_trigger to add new created Trigger to the chain
In result of every step described before there is an object TABLE that points to an object Table_trigger_dispatcher. An instance of Table_trigger_dispatcher contains a set of objects Table_trigger_chain that in turn contains a list of Trigger to execute.
Executing triggers
When user executes some DML statement for that there is assigned triggers, server does the following steps:
- Look at the object TABLE to get pointer to an instance of Table_trigger_dispatcher
- Call method Table_trigger_dispatcher::get_triggers to get an object of Table_trigger_chain for specified value of ACTION/TIMING
- If there is an object Table_trigger_chain for specified value pair ACTION/TIMING then the method Trigger_chain::execute_triggers is called to execute every trigger in the list of triggers assigned to the TABLE.