MySQL的触发器详解

196次阅读

共计 2720 个字符，预计需要花费 7 分钟才能阅读完成。

触发器—trigeer 是数据库下用于监视数据改变的某种情况，并触发执行某种操作的一个功能，简单来说就是对某一张表符合某种条件的更改前或者后执行一段存储过程。在这里就简单的说一说在 MySQL 下如何使用触发器。在使用 MySQL 的触发器可以检查数据完整性、可以捕获数据库层中业务逻辑中的错误、对于有使用审计表中数据的更改非常有用，但是它的缺点是在使用会触发器后只能提供扩展验证，并且无法替换所有验证，其次增加了后期维护成本，最后是在目前 MySQL 下触发器对服务器的开销增加比较大，建议在业务比较频繁的表中不要使用大量的触发器，在 MySQL 中触发器创建语法四要素：1. 监视对象 (大多都是对表) 2. 监视事件 (insert/update/delete) 3. 触发时间 (after/before) 4. 触发事件 (insert/update/delete)。

首先，目前的 MySQL 中在使用触发器时能对某一张表进行 insert、delete、update 操作的前或者是后为条件从而触发事先定义好的操作。这里需要注意的是在使用 MySQL 的触发器时必须要有足够大的权限至少需要用 super 用户的权限，在缺省条件下 MySQL 的触发器同 function 一样普通用户是无法进行操作的，如果需要赋予普通用户创建触发器的权限需要在开启 log_bin_trust_function_creators 变量：

mysql> SET GLOBAL log_bin_trust_function_creators = ON;# 在配置好后在 my.cnf 或 my.ini 中添加 
Query OK, 0 rows affected (0.00 sec)

在该参数开启后普通用户就也可以创建触发器和 function，其次目前的 MySQL 中触发器仅支持 DML 并不支持 DDL，其创建常用 DDL 语法如下：

CREATE TRIGGER  触发器名 
AFTER/BEFORE INSERT/UPDATE/DELETE ON  表名 FOR EACH ROW   #这句话在 MYSQL 是固定的 
 
BEGIN
DML 语句;
END;

此外还要注意在 MySQL 的触发器下的 old 于 new 的区别，以下引用网络上一段图文：

1、当使用 insert 语句的时候，如果原表中没有数据的话，那么对于插入数据后表来说新插入的那条数据就是 new

MySQL 的触发器详解

2、当使用 delete 语句的时候，删除的那一条数据相对于删除数据后表的数据来说就是 old

MySQL 的触发器详解

3、当使用 update 语句的时候，当修改原表数据的时候相对于修改数据后表的数据来说原表中修改的那条数据就是 old，而修改数据后表被修改的那条数据就是 new

MySQL 的触发器详解

所以在触发器中使用 new 和 old 还是有区别的，在这里用 2 张表说明下，有 2 张表 tbl_name 和 tbl_name_log

tbl_name 的 DDL：

DROP TABLE IF EXISTS `tbl_name`;
CREATE TABLE `tbl_name` (
  `id` int(10) NOT NULL COMMENT '姓名 ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tbl_name_log 的 DDL：

DROP TABLE IF EXISTS `tbl_name_log`;
CREATE TABLE `tbl_name_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '审计 ID',
  `action` varchar(10) NOT NULL COMMENT 'tbl_name 表中执行的操作',
  `name` varchar(50) NOT NULL COMMENT '操作的姓名',
  `time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '操作时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在 tbl_name 中分别对 insert/update/delete 三种事件后做出记录于 tbl_name_log 表中，就可以在 tbl_name 添加 3 个触发器：

DROP TRIGGER IF EXISTS `audit_name_add`;
DELIMITER ;;
CREATE TRIGGER `audit_name_add` AFTER INSERT ON `tbl_name` FOR EACH ROW #在 tbl_name 插入后在 tbl_name_log 记录日志的触发器 
BEGIN
  INSERT INTO `tbl_name_log` (`action`,`name`,`time`)
VALUES
  ('add',new.`name`,NOW());
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `audit_name_update`;
DELIMITER ;;
CREATE TRIGGER `audit_name_update` AFTER UPDATE ON `tbl_name` FOR EACH ROW #在 tbl_name 更新后在 tbl_name_log 记录日志的触发器 
BEGIN
  INSERT INTO `tbl_name_log` (`action`,`name`,`time`)
VALUES
  ('update',new.`name`,NOW());
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `audit_name_del`;
DELIMITER ;;
CREATE TRIGGER `audit_name_del` AFTER DELETE ON `tbl_name` FOR EACH ROW #在 tbl_name 删除后在 tbl_name_log 记录日志的触发器 
BEGIN
  INSERT INTO `tbl_name_log` (`action`,`name`,`time`)
VALUES
  ('del',old.`name`,NOW());
END
;;
DELIMITER ;