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

Can't reference all columns in "SHOW TRIGGERS WHERE ..."

    XMLWordPrintable

    Details

      Description

      The documentation says that "SHOW TRIGGERS" can have a "WHERE" clause to filter on columns in the results.

      https://mariadb.com/kb/en/library/show-triggers/

      So if we create a trigger from the MySQL documentation:

      CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
      CREATE TRIGGER ins_sum BEFORE INSERT ON account
      FOR EACH ROW SET @sum = @sum + NEW.amount;

      https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

      Let's see our "SHOW TRIGGERS" output:

      MariaDB [db1]> SHOW TRIGGERS\G
      *************************** 1. row ***************************
                   Trigger: ins_sum
                     Event: INSERT
                     Table: account
                 Statement: SET @sum = @sum + NEW.amount
                    Timing: BEFORE
                   Created: 2017-11-02 13:28:58.37
                  sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      1 row in set (0.00 sec)
      

      Some of these columns can be filtered on, such as Timing:

      MariaDB [db1]> SHOW TRIGGERS WHERE Timing='BEFORE'\G
      *************************** 1. row ***************************
                   Trigger: ins_sum
                     Event: INSERT
                     Table: account
                 Statement: SET @sum = @sum + NEW.amount
                    Timing: BEFORE
                   Created: 2017-11-02 13:28:58.37
                  sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      1 row in set (0.01 sec)
      

      And Statement:

      MariaDB [db1]> SHOW TRIGGERS WHERE Statement LIKE 'Set%'\G
      *************************** 1. row ***************************
                   Trigger: ins_sum
                     Event: INSERT
                     Table: account
                 Statement: SET @sum = @sum + NEW.amount
                    Timing: BEFORE
                   Created: 2017-11-02 13:28:58.37
                  sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      1 row in set (0.01 sec)
      

      But some cannot be filtered on, such as Table:

      MariaDB [db1]> SHOW TRIGGERS WHERE Table IN('account', 'user')\G
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Table IN('account', 'user')' at line 1
      

      And Trigger:

      MariaDB [db1]> SHOW TRIGGERS WHERE Trigger='ins_sum'\G
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Trigger='ins_sum'' at line 1
      

      So it looks like this functionality may be partially broken.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: