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.