- 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:
- ‘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:
- 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.
- 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:
Changes to data dictionary
Changes to error messages
Code test coverage
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
- 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.