玖叶教程网

前端编程开发入门

2024年04月25日

跟小编一起学:MySQL触发器教程（mysql触发器的三种触发事件）

1. 创建触发器语法

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

语法相关部分说明：

1.1 授权与回收

创建触发器需要有CREATE TRIGGER权限：

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

1

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

权限收回：

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

1

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

1.2 trigger_name

必须给触发器命令，最多64个字符，建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts，触发器发生在insert之前before)

1.3 DEFINER子句

在激活触发器时，检查访问权限，确保触发器安全使用。

1.4 trigger_time

定义触发器触发时间。可以设置为在行记录更改之前或之后发生。

1.5 trigger_event

定义触发器触发事件。触发的事件有：

1.5.1

INSERT：当一个新行插入到表中时触发。如INSERT、LOAD DATA和REPLACE语句。

UPDATE：当一个行数据被更改时触发。如UPDATE语句。

DELETE：当一个行从表中删除时触发。如DELETE和REPLACE语句。 注意：DROP TABLE和TRUNCATE TABLE语句不会触发该触发器，因为它们不是使用DELETE。同样删除一个分区表也不会触发。

有一个潜在的混乱情况，如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取决于是否有重复键行。

不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFORE UPDATE触发器，但是，可以创建一个BEFORE UPDATE和一个BEFORE INSERT或一个BEFORE UPDATE和一个AFTER UPDATE触发器。

1.6 FOR EACH ROW子句

定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。

1.7 trigger_body子句

包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN ... END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。

1.7.1 OLD与NEW

在触发器的SQL语句中，可以关联表中的任何列，通过使用OLD和NEW列名来标识，如OLD.col_name、NEW.col_name。OLD.col_name关联现有的行的一列在被更新或删除前的值。NEW.col_name关联一个新行的插入或更新现有的行的一列的值。

对于INSERT语句，只有NEW是合法的。否则会报错：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger

对于DELETE语句，只有OLD是合法的。否则会报错：ERROR 1363 (HY000): There is no NEW row in on DELETE trigger

对于UPDATE语句，NEW和OLD可以同时使用。

2. 实例

2.1 创建表

使用在《mysqludf_json将关系数据以JSON编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。

mysql> create table `ttlsa_users` (

-> `uid` int(11) unsigned,

-> `username` varchar(40) NOT NULL,

-> `password` varchar(40) NOT NULL,

-> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> PRIMARY KEY (`uid`)

-> );

mysql> create table `ttlsa_users` (

-> `uid` int(11) unsigned,

-> `username` varchar(40) NOT NULL,

-> `password` varchar(40) NOT NULL,

-> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> PRIMARY KEY (`uid`)

-> );

创建另外一张表来存放触发器动作数据。

mysql> create table `ttlsa_users3` (

-> `uid` int(11) unsigned,

-> `userinfo` varchar(200),

-> );

mysql> create table `ttlsa_users3` (

-> `uid` int(11) unsigned,

-> `userinfo` varchar(200),

-> );

2.2 创建触发器

mysql> delimiter //

mysql> create trigger ttlsa_users_ai

-> after insert on ttlsa_users

-> for each row

-> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));

-> //

mysql> create trigger ttlsa_users_au

-> after update on ttlsa_users

-> for each row

-> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;

-> //

mysql> delimiter //

mysql> create trigger ttlsa_users_ai

-> after insert on ttlsa_users

-> for each row

-> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));

-> //

mysql> create trigger ttlsa_users_au

-> after update on ttlsa_users

-> for each row

-> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;

-> //

2.3 测试

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

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

| uid | username | password | createtime | json_data |

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

| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |

| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh |

| 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger |

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

3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

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

| userinfo | uid |

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

| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |

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

2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

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

| uid | username | password | createtime | json_data |

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

| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |

| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh |

| 890 | xuhh | test_update | 2013-08-14 16:41:33 | test trigger |

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

3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

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

| userinfo | uid |

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

| {"uid":890,"username":"xuhh","password":"test_update"} | 890 |

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

2 rows in set (0.00 sec)

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//

Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//

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

| uid | username | password | createtime | json_data |

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

| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |

| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh |

| 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger |

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

3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

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

| userinfo | uid |

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

| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 |

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

2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users//

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

| uid | username | password | createtime | json_data |

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

| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" |

| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh |

| 890 | xuhh | test_update | 2013-08-14 16:41:33 | test trigger |

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

3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//

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

| userinfo | uid |

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

| {"uid":890,"username":"xuhh","password":"test_update"} | 890 |

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

2 rows in set (0.00 sec)

3. 管理

3.1 列出触发器

mysql> 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: [email protected]

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: [email protected]

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> 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: [email protected]

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: [email protected]

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)

3.2 INFORMATION_SCHEMA.TRIGGERS表

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: [email protected]

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: latin1_swedish_ci

1 row in set (0.00 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: [email protected]

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: latin1_swedish_ci

1 row in set (0.00 sec)

3.3 删除触发器

mysql> drop trigger trigger_name;

1

mysql> drop trigger trigger_name;

标签：mysqltruncatetable 

作者:gojiuye , 分类:技术精选 , 浏览:16 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言