【导读】
最近ITPUB技术论坛特意组织网络性讨论活动,关于数据库审计的话题,分享各自公司如何实现数据库审计、个人经验和构想，以及数据库审计的技巧,刚好有网友发了一个典型的审计需求,要帮他分析,以及教其如何实现,为此把内容整理成一片文章分享，供大家参考。
MySQL触发器的用处还是非常多地，关键看业务需要，曾经给大家介绍过基于存储引擎MEMORY加触发器的应用场景之一剖析。通过阅读本文，将会告诉大家：触发器的语法知识、触发器的限制、审计案例分析和实现，将逐一讲解。
n 语法
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：为触发器内部可执行的语句体；
n 触发器限制
l 拥有触发器的数据库对象必须为实体表，不能为临时表或视图；
l MyISAM、MEMORY、InnoDB等常用存储引擎都支持触发器功能；
l 触发器支持INSERT类操作：INSERT、LOAD DATA、REPLACE;
l 触发器支持UPDATE操作；
l 触发器支持DELETE操作，但是不支持DROP TABLE 、TRUNCATE操作；
l 处发起能支持字句：INSERT INTO … ON DUPLICATE KEY UPDATE …；
l 一个数据库中的对象表，不能对同一触发事件有2个或以上的触发器同时响应；
l 一个数据库中不能有同名的触发器程序；
l 触发器无法显示调用执行，也无法像函数或存储过程一样显示地传递参数；
l 通过关键字OLD.column_name获得的值不能通过SET命令修改，但是关键字NEW获得的值能通过SET NEW.column_name=VALUE方式修改；
l 触发器的处理部分不能含有事务的关键字，例如：COMMIT、ROLLBACK等；
l 创建了触发器的表，若支持事务，则触发器也会受事务执行成功还是失败的影响，且触发器程序执行成功还是失败，也会影响事务的执行是成功还是失败；若不支持事务，则也无法支持事务的回滚操作；
n 审计案例
有一张存储车辆收费信息的表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)
可以看到需要审计的信息，都已经存储到对应的审计表中，到此触发器实现审计功能的需求就完整实现了。
n 总结
触发器的用处非常多，关键是要结合业务场景使用，本文给大家介绍了如何使用触发器实现数据库的审计功能；我们还可以借助触发器实现2张表之间的数据同步问题，配合MEMORY引擎可以解决该存储引擎缺陷：数据无法持久化，从而增强MEMORY引擎的使用场景；我们也可以利用触发器实现异地，甚至跨国界多数据库节点之间的数据同步业务需求，后续篇章给大家介绍，某著名电子商务公司利用触发器，加应用程序实现多数据节点之间的数据同步问题。
文章原地址：http://www.mysqlops.com/2011/07/08/mysql-trigger-audit.html