IntroductionFor 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 overviewIn 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.
- trigger t1_bi, BEFORE INSERT ON t1
- trigger t1_bu, BEFORE UPDATE ON t1
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 ...'
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.
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>
- 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 ....
+--------------------+---------------------+| 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 dictionaryCurrently 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 messagesThe 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 coverage21 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 detailsImplementation 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.