Details
Minor 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.