MySQL触发器的用处还是非常多地，关键看业务需要，曾经给大家介绍过基于存储引擎MEMORY加触发器的应用场景之一剖析。通过阅读本文，将会告诉大家：触发器的语法知识、触发器的限制、审计案例分析和实现，将逐一讲解。

　　语法

CREATE 　　[DEFINER = { user | CURRENT_USER }] 　　TRIGGER trigger_name trigger_time trigger_event 　　ON tbl_name FOR EACH ROW trigger_stmt

　　DEFINER：指定触发器的创建者，默认为登录mysqld服务器的账号信息;

　　trigger_name：触发器的名称，要符合mysql对待数据库对象命名的规范;

　　trigger_time：触发表上的触发器语句体执行的时间：行更新前还是行更新后，2个选项值：

　　BEFOR or AFTER;

　　tbl_name：指定触发器是对应那一个数据库对象：表的;

　　trigger_stmt：为触发器内部可执行的语句体;

　　触发器限制

• 　　拥有触发器的数据库对象必须为实体表，不能为临时表或视图;
• 　　MyISAM、MEMORY、InnoDB等常用存储引擎都支持触发器功能;
• 　　触发器支持INSERT类操作：INSERT、LOAD DATA、REPLACE;
• 　　触发器支持UPDATE操作;
• 　　触发器支持DELETE操作，但是不支持DROP TABLE 、TRUNCATE操作;
• 　　处发起能支持字句：INSERT INTO … ON DUPLICATE KEY UPDATE …;
• 　　一个数据库中的对象表，不能对同一触发事件有2个或以上的触发器同时响应;
• 　　一个数据库中不能有同名的触发器程序;
• 　　触发器无法显示调用执行，也无法像函数或存储过程一样显示地传递参数;
• 　　通过关键字OLD.column_name获得的值不能通过SET命令修改，但是关键字NEW获得的值能通过SET NEW.column_name=VALUE方式修改;
• 　　触发器的处理部分不能含有事务的关键字，例如：COMMIT、ROLLBACK等;
• 　　创建了触发器的表，若支持事务，则触发器也会受事务执行成功还是失败的影响，且触发器程序执行成功还是失败，也会影响事务的执行是成功还是失败;若不支持事务，则也无法支持事务的回滚操作;

　　审计案例

　　有一张存储车辆收费信息的表t_car，因业务要求，程序要有对该表的数据修改权限，为此需要审计对该表上数据的记录值修改信息，以备查询、跟踪。表t_car结构：

CREATE TABLE t_car( 　　`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, 　　`car_number` VARCHAR(45) DEFAULT ” COMMENT ‘车牌号’, 　　`card_number` VARCHAR(45) DEFAULT ” COMMENT ‘卡片编号’, 　　`pay` DECIMAL(6,1) DEFAULT 0 COMMENT ‘金额’, 　　PRIMARY KEY(`id`) 　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　审计存储的内容：

　　修改前pay字段的值;

　　修改后pay字段的值;

　　记录被修改的时间;

　　登陆数据库服务器修改数据的ip地址、帐号信息;

　　为此审计表的结构为：

CREATE TABLE t_record( 　　`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, 　　`username` VARCHAR(45) DEFAULT ” COMMENT ‘登录mysql的用户名’, 　　`client_ip` VARCHAR(45) DEFAULT ” COMMENT ‘远程访问mysql服务器的客户端ip地址’, 　　`update_Before` VARCHAR(45) DEFAULT ” COMMENT ‘修改前的金额’, 　　`update_After` VARCHAR(45) DEFAULT ” COMMENT ‘修改后的金额’, 　　`gmt_create` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘创建时间’, 　　PRIMARY KEY (`id`) 　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　我们可以使用触发器记录所有用户对表t_car，进行UPDATE操作修改数据的行为进行记录，触发器语句体：

DELIMITER $$ 　　CREATE TRIGGER tri_t_car BEFORE UPDATE ON t_car FOR EACH ROW 　　BEGIN 　　IF NEW.pay<>OLD.pay THEN 　　INSERT INTO t_record(username,client_ip,update_Before,update_After,gmt_create) 　　VALUES(SUBSTRING_INDEX(USER(),’@’,1),SUBSTRING_INDEX(USER(),’@’,-1),OLD.pay,NEW.pay,NOW()); 　　END IF; 　　END $$ 　　DELIMITER ;

　　测试：

　　测试用例的数据生成语句：

INSERT INTO t_car(car_number,card_number,pay) VALUES(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3)), (SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3)), (SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3)); root@localhost : test 11:14:49> SELECT * FROM t_car; +—-+—————–+————-+——-+ | ID | car_number      | card_number | pay   | +—-+—————–+————-+——-+ |  1 | 933606902075565 | 4065322181  | 231.0 | |  2 | 939605452064057 | 0025060456  | 193.0 | |  3 | 96105140723386  | 2241153588  | 237.0 | +—-+—————–+————-+——-+ 3 rows in set (0.00 sec)

　　修改目标表数据的测试语句：

　　UPDATE t_car SET pay=100.5 WHERE ID=1;

　　查询审计信息存储的表：

root@localhost : test 11:15:51> SELECT * FROM t_record; +—-+———-+———–+—————+————–+———————+ | ID | username | client_ip | update_Before | update_After | gmt_create          | +—-+———-+———–+—————+————–+———————+ |  1 | root     | localhost | 231.0         | 100.5        | 2011-07-08 11:15:51 | +—-+———-+———–+—————+————–+———————+ 1 row in set (0.00 sec)

　　可以看到需要审计的信息，都已经存储到对应的审计表中，到此触发器实现审计功能的需求就完整实现了。

　　总结

　　触发器的用处非常多，关键是要结合业务场景使用，本文给大家介绍了如何使用触发器实现数据库的审计功能;我们还可以借助触发器实现2张表之间的数据同步问题，配合MEMORY引擎可以解决该存储引擎缺陷：数据无法持久化，从而增强MEMORY引擎的使用场景;我们也可以利用触发器实现异地，甚至跨国界多数据库节点之间的数据同步业务需求，后续篇章给大家介绍，某著名电子商务公司利用触发器，加应用程序实现多数据节点之间的数据同步问题。