Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10911

Make SHOW TRIGGERS to show triggers' action order

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Fix
    • Fix Version/s: N/A
    • Component/s: Triggers
    • Labels:
      None

      Description

      Currently SHOW TRIGGERS does not show the action order of triggers.

      MariaDB [test]> show triggers like 't1' \G
      *************************** 1. row ***************************
                   Trigger: tr1
                     Event: INSERT
                     Table: t1
                 Statement: insert into tlog values (now(),'INSERT',new.c)
                    Timing: AFTER
                   Created: 2016-09-28 01:42:07.43
                  sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      *************************** 2. row ***************************
                   Trigger: tr0
                     Event: INSERT
                     Table: t1
                 Statement: insert into tlog values (now(),'INSERT',new.c)
                    Timing: AFTER
                   Created: 2016-09-28 01:42:23.27
                  sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      *************************** 3. row ***************************
                   Trigger: tr2
                     Event: INSERT
                     Table: t1
                 Statement: insert into tlog values (now(),'INSERT',new.c)
                    Timing: AFTER
                   Created: 2016-09-28 01:36:05.20
                  sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      3 rows in set (0.01 sec)
      

      It's not critical, since it can be found from INFORMATION_SCHEMA.TRIGGERS:

      MariaDB [test]> select * from information_schema.triggers \G
      *************************** 1. row ***************************
                 TRIGGER_CATALOG: def
                  TRIGGER_SCHEMA: test
                    TRIGGER_NAME: tr1
              EVENT_MANIPULATION: INSERT
            EVENT_OBJECT_CATALOG: def
             EVENT_OBJECT_SCHEMA: test
              EVENT_OBJECT_TABLE: t1
                    ACTION_ORDER: 1
                ACTION_CONDITION: NULL
                ACTION_STATEMENT: insert into tlog values (now(),'INSERT',new.c)
              ACTION_ORIENTATION: ROW
                   ACTION_TIMING: AFTER
      ACTION_REFERENCE_OLD_TABLE: NULL
      ACTION_REFERENCE_NEW_TABLE: NULL
        ACTION_REFERENCE_OLD_ROW: OLD
        ACTION_REFERENCE_NEW_ROW: NEW
                         CREATED: 2016-09-28 01:42:07.43
                        SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                         DEFINER: root@localhost
            CHARACTER_SET_CLIENT: utf8
            COLLATION_CONNECTION: utf8_general_ci
              DATABASE_COLLATION: latin1_swedish_ci
      *************************** 2. row ***************************
                 TRIGGER_CATALOG: def
                  TRIGGER_SCHEMA: test
                    TRIGGER_NAME: tr0
              EVENT_MANIPULATION: INSERT
            EVENT_OBJECT_CATALOG: def
             EVENT_OBJECT_SCHEMA: test
              EVENT_OBJECT_TABLE: t1
                    ACTION_ORDER: 2
                ACTION_CONDITION: NULL
                ACTION_STATEMENT: insert into tlog values (now(),'INSERT',new.c)
              ACTION_ORIENTATION: ROW
                   ACTION_TIMING: AFTER
      ACTION_REFERENCE_OLD_TABLE: NULL
      ACTION_REFERENCE_NEW_TABLE: NULL
        ACTION_REFERENCE_OLD_ROW: OLD
        ACTION_REFERENCE_NEW_ROW: NEW
                         CREATED: 2016-09-28 01:42:23.27
                        SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                         DEFINER: root@localhost
            CHARACTER_SET_CLIENT: utf8
            COLLATION_CONNECTION: utf8_general_ci
              DATABASE_COLLATION: latin1_swedish_ci
      *************************** 3. row ***************************
                 TRIGGER_CATALOG: def
                  TRIGGER_SCHEMA: test
                    TRIGGER_NAME: tr2
              EVENT_MANIPULATION: INSERT
            EVENT_OBJECT_CATALOG: def
             EVENT_OBJECT_SCHEMA: test
              EVENT_OBJECT_TABLE: t1
                    ACTION_ORDER: 3
                ACTION_CONDITION: NULL
                ACTION_STATEMENT: insert into tlog values (now(),'INSERT',new.c)
              ACTION_ORIENTATION: ROW
                   ACTION_TIMING: AFTER
      ACTION_REFERENCE_OLD_TABLE: NULL
      ACTION_REFERENCE_NEW_TABLE: NULL
        ACTION_REFERENCE_OLD_ROW: OLD
        ACTION_REFERENCE_NEW_ROW: NEW
                         CREATED: 2016-09-28 01:36:05.20
                        SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                         DEFINER: root@localhost
            CHARACTER_SET_CLIENT: utf8
            COLLATION_CONNECTION: utf8_general_ci
              DATABASE_COLLATION: latin1_swedish_ci
      3 rows in set (0.01 sec)
      

      Still, it would make sense to have it in SHOW TRIGGERS.

      Update:
      It turns out that the MySQL's MTR test case actually has a test which checks, specifically, that ACTION_ORDER is not displayed in SHOW output. I am not sure why, though.

      # Test 5.
      # Check that action_order attribute isn't shown 
      # in the output of SHOW TRIGGERS and SHOW CREATE TRIGGER
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: