WL#3253: Triggers: multiple triggers per table

Affects: Server-5.7   —   Status: Complete   —   Priority: Low

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.49 <trigger definition>
  Page: 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 <trigger name>

  - PRECEDES <trigger name>

  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: <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
  ...

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.