WL#3253: Triggers: multiple triggers per table
Affects: Server-5.7
—
Status: Complete
The server currently supports only one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). In other words, there might be at most one trigger for every pair (action, timing). This WL task is about providing the possibility to have more than one trigger for every action and timing.
The main question in this task is the order of trigger activation. Apart from being convenient for the users, that order should be preserved during replication, dump/restore and backup scenarios. Quotes from The SQL Standard (2011 draft) ----------------------------------------- - File: 7IWD1-02-Foundation-2011-07.pdf (2011 draft) Section: 4.39 Triggers Page: 145 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. - File: 7IWD1-02-Foundation-2011-07.pdf (2011 draft) Section: 11.49Page: 743 - File: 7IWD1-11-Schemata-2011-07.pdf - Section: 5.3 CARDINAL_NUMBER domain Page:11 CREATE DOMAIN CARDINAL_NUMBER AS INTEGER CONSTRAINT CARDINAL_NUMBER_DOMAIN_CHECK CHECK ( VALUE >= 0 ); - Section: 5.6 TIME_STAMP domain Page: 14 CREATE DOMAIN TIME_STAMP AS TIMESTAMP(2) WITH TIME ZONE; - Section: 6.62 TRIGGERS base table Page: 245 CREATE TABLE TRIGGERS ( ... ACTION_ORDER INFORMATION_SCHEMA.CARDINAL_NUMBER CONSTRAINT TRIGGERS_ACTION_ORDER_NOT_NULL NOT NULL, ... CREATED INFORMATION_SCHEMA.TIME_STAMP, ... - Section: 6.62 TRIGGERS base table Page: 246 9) The value of ACTION_ORDER is the ordinal position of the trigger in the list of triggers with the same EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, EVENT_MANIPULATION, CONDITION_TIMING, and ACTION_ORIENTATION Proposal -------- * Introduce a new trigger attribute -- CREATED: - CREATED is a read-only attribute, storing the current time at trigger creation (timestamp of trigger creation); NOTE: the current time can be overridden by 'SET TIMESTAMP'. - CREATED is a persistent attribute, stored in the data dictionary (currently in TRG-files); - CREATED is a pure informational attribute. Strictly speaking, it is not required in order to complete this WL task. It's proposed to introduce CREATED only because: - it is required by The Standard; - we already have INFORMATION_SCHEMA.TRIGGERS.CREATED field, which is always NULL. - The value of the CREATED attribute 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); - CREATED is shown in the INFORMATION_SCHEMA.TRIGGERS.CREATED (INFORMATION_SCHEMA.TRIGGERS.CREATED exists currently, but is always NULL); - A new column (created) is added to the SHOW CREATE TRIGGER result-set to show CREATED; - Internally CREATED is represented by TIMESTAMP(2) (according to The SQL Standard) - According to The Standard CREATED should include timezone information. However, MySQL TIMESTAMP/DATETIME don't support timezone information. Thus, it's suggested to store timestamps in the UTC timezone. However, it's proposed that INFORMATION_SCHEMA.TRIGGERS (SHOW TRIGGERS) and SHOW CREATE TRIGGER output CREATED in the session timezone. This is in line with the current behaviour of CREATED column in INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.EVENTS and INFORMATION_SCHEMA.TABLES. - It is possible to create triggers with the same CREATED values. * Introduce a new trigger attribute -- ACTION_ORDER -- the order of trigger activation: - ACTION_ORDER is a persistent attribute. The point is that the order of triggers must remain the same between user sessions / server restarts. However, for the current data dictionary (attribute lists in TRG-files) explicit storing of ACTION_ORDER is not required -- the order is well-defined (preserved) by the order of values in the TRG-attribute-lists. - ACTION_ORDER is a positive integer (greater than 0). The Standard prescribes that the ACTION_ORDER is a non-negative integer. It's proposed to not use zero value in order to distinguish between older versions, which don't support multiple triggers. - The value of the ACTION_ORDER is calculated automatically (increased) by the server at the trigger creation time; - User has no way to specify/change the exact value of the ACTION_ORDER (ACTION_ORDER is a read-only attribute); - Triggers are activated in the ascending order of ACTION_ORDER values. This statement fully complies with The Standard: - The Standard requires trigger activation in the order of created timestamp. This is satisfied as the ACTION_ORDER value is increased every time a new trigger is created. - The Standard does not specify behavior for triggers with the same created value (it's implementation-defined). This statement is semantically equal to the following: triggers are activated in the ascending order of their creation. Note, that MySQL provides a way to override current time: 'SET TIMESTAMP'. In the current proposal, overriding the current time will affect value of the CREATED attribute, but will not affect value of the ACTION_ORDER attribute. - ACTION_ORDER is shown in the INFORMATION_SCHEMA.TRIGGERS.ACTION_ORDER (INFORMATION_SCHEMA.TRIGGERS.ACTION_ORDER exists currently, but is always 0). - SHOW TRIGGERS does not show ACTION_ORDER; - SHOW CREATE TRIGGER does not show ACTION_ORDER; - ACTION_ORDER values might be implicitly changed by the server (see below); * mysqldump must dump triggers in the ascending order of ACTION_ORDER attribute. This order ensures that during the restore triggers will be re-created in the same order. * mysqldump will not preserve trigger creation timestamps (there will be no 'SET TIMESTAMP' statement before 'CREATE TRIGGER'). This is the common mysqldump behavior for other object types -- mysqldump does not prevent creation timestamps for tables, stored programs, ... * Additionally, the server will be extended to support the following non-standard extensions to the CREATE TRIGGER statement (Oracle 11g style): - FOLLOWS - PRECEDES For example: CREATE TRIGGER t1_ai_1a AFTER INSERT ON t1 FOR EACH ROW FOLLOWS t1_ai_1 BEGIN ... END CREATE TRIGGER t1_ai_1a AFTER INSERT ON t1 FOR EACH ROW PRECEDES t1_ai_2 BEGIN ... END The semantic is as follows: - 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 might result in the re-numbering of ACTION_ORDER values for the existing triggers. However, the activation order remains the same. - FOLLOWS/PRECEDES will not appear in any auto-generated SQL. - FOLLOWS/PRECEDES are optional. Note that FOLLOWS/PRECEDES changes the statement "triggers are activated in the ascending order of their creation". Rationale --------- * Why two different attributes? An alternative approach could be to use only CREATED attribute to define activation order. However, there are the following problems with that approach: - The main problem is that creation timestamp might be the same for different triggers. It might happen for various reasons, the worst case is scripting -- triggers created by the SQL script might have (or might not, depending on the box capabilities and current load) same timestamps. - If we're going to support FOLLOWS/PRECEDES, we have to fake creation time, which is an ugly hack. In other words, there is no good way to support FOLLOWS/PRECEDES with CREATED attribute only. - Generally, in this approach, two different concepts are mixed in the CREATED attribute: - it is creation timestamp; - it is an index to specify activation order; Usually, mixing concepts complicates things. Changes to data dictionary -------------------------- Currently triggers are stored in TRG (TRN) files. Main definitions are stored in the TRG-file (format: .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 ... Two new attributes will be added to the TRG file: - created -- the attribute will contain a list of timestamps - action_order -- the attribute will contain a list of numbers The server does not check if there are more than one trigger for every action/event in the 'triggers' attribute, meaning TRG-files can already store multiple triggers for the same action/event, and that will not crash the server. However, the order of trigger definitions does matter: 1. triggers can be stored in the natural order (in the order they were created, or in the ascending order of their action_order attributes); 2. triggers can be stored in the reverse order. The difference between these two approaches is described in the "Downgrade scenarios" section. The proposal is to use (1), the natural order. Changes to the error messages ----------------------------- Currently the server throws ER_NOT_SUPPORTED_YET (1235 / 42000) as follows if the user attempts 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' This message will not appear any longer after this WL. The error message itself (ER_NOT_SUPPORTED_YET) will of course remain. Impact on replication --------------------- Replication should preserve ACTION_ORDER and CREATED attributes. CREATED is preserved naturally in binlog. CREATE TRIGGER statements are logged in the order user entered them, so ACTION_ORDER is preserved. Note, that replicating on the slave, which does not support multiple triggers, will result in an error when the 2nd trigger is created on the master. Impact on backup ---------------- This is the same as with replication: - CREATE TRIGGER should be prepended by SET TIMESTAMP; - CREATE TRIGGER must be backed up in the ascending order of ACTION_ORDER; - Restoring on the server, which does not support multiple triggers, will result in an error. Upgrade scenarios ----------------- Let's consider a case of in-place upgrade, i.e. - the user has database created in the previous version, which does not support multiple triggers per table; - then, the user starts the new server (which supports multiple triggers per table) against the old database. The user will notice the following changes: - SHOW CREATE TRIGGER statement output will have CREATED column, but it will contain NULL values; INFORMATION_SCHEMA.TRIGGERS.CREATED will have null values for all [old] triggers. It's been decided to have no warnings on server startup if new attributes (CREATED and/or ACTION_ORDER) are missing. Those warnings must be issued once during the upgrade procedure. Technically, the upgrade procedure involves running of the mysql_upgrade tool, which eventually leads to executing CHECK TABLE FOR UPGRADE for each table being upgraded. The upgrade warnings will be issued by the CHECK TABLE FOR UPGRADE statement. Note that in the distributed environment (when replication is used), all nodes must be upgraded before multiple triggers can be used in the system. Otherwise, older nodes will not be able to create multiple triggers, and the replication will fail with an error. Downgrade scenarios ------------------- Let's consider a case of in-place upgrade, i.e. - the user has database created in newer version, which supports multiple triggers per table; - that database has multiple triggers; - then the user starts the older server (which does not support multiple triggers per table) against that database. On server startup there will be no warnings, because triggers are loaded when tables are opened. The behaviour in this case is solely depends on the order in which triggers are stored in the TRG file: - the server just reads triggers from TRG file in the file order (in the order triggers written in the file); - if there are multiple triggers for the same action/event/table, the last trigger will take effect. Technically, the server just reads next trigger definition and assigns it to some variable corresponding to the particular action/event pair. So, if there are multiple triggers, every following trigger will override definition of the previous one. Thus, the last trigger wins. As it is stated in the "Changes to data dictionary", there are two options: 1. Triggers are stored in the natural (ascending) order. In this case, only the last trigger will be executed on the older systems. 2. Triggers are stored in the reverse order. In this case, only the first trigger will be executed on the older systems. Anyway, only one of many triggers will be executed. The option (1) will be implemented, meaning that only the last trigger will be visible (and eventually will be executed) on the older systems. Other triggers will remain in TRG-files, but the server will not see them. The TRG-file with the triggers for a given table is re-created when any of those triggers is changed, or a new trigger is added for the table. Such a change on the older system will result in a complete loss of not-visible triggers. In order do the correct downgrade, multiple triggers must be converted into a single trigger before switching to the older server. That can be done as follows: - for every trigger: create a stored function, which contains all the code in the corresponding trigger. NEW and OLD pseudo rows can be passed by arguments. - drop all triggers; - create one trigger, which subsequently invokes the stored functions. Impact on dump/restore scenarios -------------------------------- As it has been stated above, mysqldump dumps triggers in the ascending order of their ACTION_ORDER values. - If the dump is played on the newer server (which supports multiple triggers), this order ensures that triggers will be re-created in the same order. - If the dump is played on the older server (which does not support multiple triggers), an error will occur when the 2nd trigger will be processed. The restore process will fail. The CREATED attribute is not preserved in dump/restore, meaning that after the restore, triggers will have new (actual) CREATED values. This is in line with the mysqldump behavior for other objects types such as tables, stored programs, ... Functional requirements ----------------------- - SELECT CREATED FROM INFORMATION_SCHEMA.TRIGGERS must be not-null for triggers created in the versions with WL#3253; - SELECT CREATED FROM INFORMATION_SCHEMA.TRIGGERS must be null for triggers created in the versions without WL#3253; - SELECT CREATED FROM INFORMATION_SCHEMA.TRIGGERS must return timestamp in the session timezone; - SHOW CREATE TRIGGER output must have the CREATED column, which - contains not-null values for the triggers, created in the version with WL#3253; - contains null values for the triggers, created in the versions without WL#3253. - The CREATED column in the SHOW CREATE TRIGGER output must be in the sesion timezone; - It must be possible to create two trigger with the same timestamp. In other words, the following sequence of operations must succeed: SET TIMESTAMP = 1346337391; CREATE TRIGGER t1_ai_1 AFTER INSERT ON t1 FOR EACH ROW ... ; CREATE TRIGGER t1_ai_2 AFTER INSERT ON t1 FOR EACH ROW ... ; - SELECT ACTION_ORDER FROM INFORMATION_SCHEMA.TRIGGERS must always return non-negative integer number; - Triggers created subsequently, must have different ACTION_ORDER, even if they were created at the same time. Moreover, the ACTION_ORDER value of the second trigger must be greater then one of the first trigger. In other words: SET TIMESTAMP = 1346337391; CREATE TRIGGER t1_ai_1 AFTER INSERT ON t1 FOR EACH ROW ... ; CREATE TRIGGER t1_ai_2 AFTER INSERT ON t1 FOR EACH ROW ... ; ACTION_ORDER of t1_ai_1 must be less than ACTION_ORDER of t1_ai_2. - Triggers must be activated in the ascending order of ACTION_ORDER. In other words, if there are a few triggers for the same table with the same activation time and event, they must be activated in the ascending order of the ACTION_ORDER attribute. - mysqldump must dump triggers in the ascending order of ACTION_ORDER. - The FOLLOWS clause must work even if there are existing triggers: CREATE TRIGGER t1_ai_1 AFTER INSERT ON t1 FOR EACH ROW ... ; CREATE TRIGGER t1_ai_2 AFTER INSERT ON t1 FOR EACH ROW ... ; Now, there are two after-insert triggers for t1; the activation order is: 1) t1_ai_1, 2) t1_ai_2. CREATE TRIGGER t1_ai_1a AFTER INSERT ON t1 FOR EACH ROW FOLLOWS t1_ai_1 ... ; Now, there must be three after-insert triggers for t1, activated in the following order: 1) t1_ai_1, 2) t1_ai_1a, 3) t1_ai_2. - The PRECEDES clause must work even if there are existing triggers: CREATE TRIGGER t1_ai_1 AFTER INSERT ON t1 FOR EACH ROW ... ; CREATE TRIGGER t1_ai_2 AFTER INSERT ON t1 FOR EACH ROW ... ; Now, there are two after-insert triggers for t1; the activation order is: 1) t1_ai_1, 2) t1_ai_2. CREATE TRIGGER t1_ai_1a AFTER INSERT ON t1 FOR EACH ROW PRECEDES t1_ai_2 ... ; Now, there must be three after-insert triggers for t1, activated in the following order: 1) t1_ai_1, 2) t1_ai_1a, 3) t1_ai_2. - CREATE TRIGGER statement must fail if the trigger specified in the FOLLOWS clause does not exist; - CREATE TRIGGER statement must fail if the trigger specified in the PRECEDES clause does not exist; - Action order and CREATED must be preserved during replication of multiple triggers from master to slave; User Documentation ------------------ Affected sections of reference manual: http://dev.mysql.com/doc/refman/5.7/en/triggers.html http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html http://dev.mysql.com/doc/refman/5.7/en/show-create-trigger.html http://dev.mysql.com/doc/refman/5.7/en/show-triggers.html http://dev.mysql.com/doc/refman/5.7/en/triggers-table.html http://dev.mysql.com/doc/refman/5.7/en/replication-features-triggers.html http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html http://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html It's suggested to do the following refactoring of the Table_triggers_list class first: - introduce a new class (Trigger) to encapsulate trigger properties, so that one instance of the Trigger class represents one trigger object. - use that class in Table_triggers_list.Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.