mysql触发器获取当前数据_如何获取所有MySQL触发器以及仅适用于当前数据库的触发器...

要获取所有MySQL触发器，以下是语法-select trigger_schema,trigger_name from information_schema.triggers;

让我们实现以上语法以获取所有触发器名称以及模式-mysql> select trigger_schema,trigger_name from information_schema.triggers;

这将产生以下输出-+----------------+---------------------------------+

| TRIGGER_SCHEMA | TRIGGER_NAME                    |

+----------------+---------------------------------+

| sys            | sys_config_insert_set_user      |

| sys            | sys_config_update_set_user      |

| business       | binsertTrigger                  |

| business       | insertBef                       |

| business       | Table1Trigger                   |

| test           | insertingTrigger                |

| sample         | InsertPrevent_TriggerDemo       |

| sample         | InsertPreventTrigger            |

| sample         | before_Client_insert            |

| sample         | enforce_phone_check             |

| sample         | default_current_year            |

| sample         | restrictUpdateDemo              |

| web            | lowerCaseOnInsertDemo           |

| web            | preventing_to_insert_zero_value |

+----------------+---------------------------------+

14 rows in set (0.00 sec)

如果只希望当前数据库触发器，则可以使用以下查询。假设当前数据库是“网络”-mysql> use web;

Database changed

mysql> show triggers;

这将产生以下输出-+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+

| Trigger                        | Event   | Table        | Statement                                                                                                                   | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |

+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+

| lowerCaseOnInsertDemo         | INSERT | demotable222 | SET NEW.StudentSubject = LOWER(NEW.StudentSubject) | BEFORE | 2019-06-01 15:53:09.29 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | utf8_unicode_ci |

| preventing_to_insert_zero_value | INSERT | DemoTable | begin

if(new.Value = 0) then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can not provide 0 value';

END if;

end | BEFORE | 2019-07-07 18:03:41.65 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | utf8 | utf8_general_ci | utf8_unicode_ci |

+---------------------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+

2 rows in set (0.00 sec)