mysql 触发器trigeer_MySQL触发器的应用

MySQL触发器是在5.0版本引入的。针对需要对数据库做级联更改是很适宜使用的，如需要实时监控某张表中的某个字段的更改而需要做出相应的处理。触发器虽然功能强大，能轻松可靠的实现许多复杂的功能，《使用MySQL UDFs来调用gearman分布式任务分发系统》、《sphinx应用(三)—sphinx分布式, XML数据源, Gearman, UpdateAttributes》以及后续会将用户表导入到NOSQL数据库中，都使用到触发器的。不过，要慎用触发器，滥用触发器会造成数据库以及应用程序维护困难。

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 }

1

2

3

4

5

6

7

8

9

CREATE

[DEFINER={user|CURRENT_USER}]

TRIGGERtrigger_name

trigger_timetrigger_event

ONtbl_nameFOREACHROW

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

grantcreatetriggeron`database_naem`.`table_name`to`user_name`@`ip_address`;

权限收回：

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

1

revokecreatetriggeron`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`)

-> );

1

2

3

4

5

6

7

mysql>createtable`ttlsa_users`(

->`uid`int(11)unsigned,

->`use