返回
首页
业界
电商
创业
访谈
手机
移动
报告
运营
建站
互联网+
系统
教程
搜索

MySQL中触发器的基础学习教程

2019-01-04 18:23:20于丽


3.2 INFORMATION_SCHEMA.TRIGGERS表

*************************** 1. row *************************** Trigger: ttlsa_users_ai sql_mode: NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.01 sec)
sql> SHOW TRIGGERS like '%ttlsa%'; #触发器名称匹配%ttlsa%
*************************** 1. row *************************** Trigger: ttlsa_users_ai Event: INSERT Table: ttlsa_users Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) Timing: AFTER Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@127.0.0.1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci *************************** 2. row *************************** Trigger: ttlsa_users_au Event: UPDATE Table: ttlsa_users Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid Timing: AFTER Created: NULL sql_mode: NO_ENGINE_SUBSTITUTION Definer: root@127.0.0.1 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci 2 rows in set (0.00 sec)
mysql> SHOW TRIGGERS; #列出所有 mysql> SHOW TRIGGERS from database_name; #列出数据库的触发器 mysql> SHOW CREATE TRIGGER trigger_name; #查看创建触发器
*************************** 1. row *************************** Trigger: ttlsa_users_ai sql_mode: NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password)) character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.01 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'G
相关文章 大家在看
*************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: ttlsa_users_au EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: ttlsa_users ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid 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: NULL SQL_MODE: NO_ENGINE_SUBSTITUTION DEFINER: root@127.0.0.1 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec)