Support for multiple triggers per table for the same value of action/timing.

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

1. On the last step of table opening process an instance of class  Table_trigger_dspatcher is created and assigned to an object TABLE
2. The method Table_trigger_dspatcher::check_n_load is called
1. 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
2. For every unique pair action/timing for that triggers were loaded from Data Dictionary an instance of Trigger_chain is created.
3. The method Trigger_chain::add_trigger is called for every trigger with the same action/timing value.

Handling of statement CREATE TRIGGER

1. 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.
2. The method Table_trigger_dispatcher::create is called that does the following steps
1. Create new object of Trigger class
2. 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.
3. 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:

1. Look at the object TABLE to get pointer to an instance of Table_trigger_dispatcher
2. Call method Table_trigger_dispatcher::get_triggers to get an object of Table_trigger_chain for specified value of ACTION/TIMING
3. 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.

BEFORE triggers and NOT NULL columns in MySQL

Introduction

  For a long time there was a Bug#6295 in implementation of BEFORE triggers related to handling of NOT NULL column. The problem was that if a column is declared as NOT NULL, it wasn't possible to do INSERT NULL (or UPDATE to NULL) even though there was associated trigger, setting NOT-NULL value.

For example:

• There is the table 't1' with a NOT NULL column 'c1'
• The table has BEFORE INSERT trigger which sets the 'c1' column
  to NOT NULL value (SET NEW.c1 = 1)
• User executes the SQL statement INSERT INTO t1 VALUES(NULL) that fails with the following error:
      ERROR 1048 (23000): Column 'c1' cannot be null
• The user will get the same error if there is a BEFORE UPDATE trigger that sets the 'c1' column to NOT NULL and  the statement UPDATE t1 SET c1=NULL is executed

The reason for such wrong behavior was that the column constraints were checked before trigger were executed. Such behavior violated the SQL Standard that requires that column constraints are checked at the end of an SQL statement.

To fix this issue we redesigned the mysql server in the way that:

• the server allows NOT NULL columns to be NULL temporarily. That means, every NOT NULL column must be able to store NULL value  temporarily during statement execution;
• at the end of INSERT / UPDATE statement (after executions of all associated triggers) the server checks every NOT NULL in the record to ensure that NOT NULL constraint is valid;
• if there is NULL among NOT-NULL fields, the server reports an error, otherwise passes the record to a Storage Engine.

Note that:

• a NOT-NULL field can be a part of complex SELECT statement, in this case it still should be treated as NOT NULL, otherwise the optimizer  might choose a wrong plan;
• a NOT-NULL field can be a part of the SET and WHERE parts of an UPDATE statement, and it should be treated as nullable in the SET part, and as NOT NULL in the WHERE part;
• it should be possible to check nullability of a NOT-NULL field inside trigger body and it should work as if the field is nullable:
• For example, it should be possible to have expressions like (NEW.col IS NULL) when the column 'col' is declared as NOT NULL.

This wrong behavior was fixed in version 5.7.1.

Changes in handling of warnings

    For the strict sql_mode we added grouping of warning messages by column for the following error codes:

• ER_BAD_NULL_ERROR
• ER_NO_DEFAULT_FOR_FIELD
• ER_NO_DEFAULT_FOR_VIEW_FIELD.

  It means that any such warning message will be shown at most once for every column during handling of statement INSERT/REPLACE/INSERT SELECT/CREATE SELECT. In the strict sql_mode server reports an error for first occurrence of correspond violation and terminates statement execution. Before this the server wrote warning message corresponded to error codes mentioned above for every row that provoked this error. So, for example, if an user executed the statement INSERT SELECT for a table with a NOT NULL column and for this column the user sets the value NULL then the server generated as many warnings as there were rows that violated NOT NULL constraint. For LOAD STATEMENT the behavior remains the same as before since it makes sense to know what lines of imported file generates each line of warning.

Code test coverage

Around 40 new MTR tests were added during implementation of new functionality. These test are public and reside in mysql-test/t/trigger_wl6030.test. Implemented changes were 100% covered by these tests.

Technical details

To solve the task described in the introduction we decided to implement the following approach:

• Introduce two new attributes into Field:
• Field::m_is_tmp_nullable -- a flag, indicating if this particular field can accept NULL even though it is declared as NOT NULL;
• Field::m_is_tmp_null -- a place to store NULL value in case of NOT NULL field.
• Keep the physical table's record [table row buffer] intact;
• Introduce a new method Field::is_tmp_nullable() and use it in cases when we have to check whether NULL value can be assigned to this Field object;
• Change Field::is_null() so that it takes into account is_tmp_nullable and is_tmp_null;
• Extend interface of class Field by method check_constraints() in order to check NOT NULL constraint for the field.

When mysql server handles INSERT statement it iterates along the rows involved by the statement, sets defaults values for appropriate fields (if any) and calls the function fill_record_and_invoke_before_triggers(). This function does all things that are necessary to  complete the statement execution. For case when the table referenced by INSERT statement has BEFORE INSERT trigger this function does the following steps:

• For every field affected by the statement sets temporary nullability flag by calling the method Field::set_tmp_nullable();
• For every field affected by the statement sets the value specified by the statement for this field;
• In case when the value being set is NULL the function set_field_to_null_with_conversions() is called to handle this case;
• This function sets temporary NULL value for those fields that are declared as NOT NULL;
• Calls the BEFORE INSERT trigger;
• For every field affected by the statement clears temporary nullability flag by calling the method Field::reset_tmp_nullable();
• Calls the function check_record() to check NOT NULL constraint for every field involved by the statement being executed;
• The function check_record()  iterates along the Field objects and calls the method Field::check_constraints() for every one;
• The method Field::check_constraints() checks whether corresponding field can be NULL. If it is true the the method returns ok. If corresponding field in the table can't be NULL and Field::m_is_tmp_null is set then the error returned and corresponding reason is set in Diagnostics area.

Based on performance measurements the introduction of two additional attributes to the class Field didn't lead to performance degradation.