触发器—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 变量:
在该参数开启后普通用户就也可以创建触发器和 function,其次目前的 MySQL 中触发器仅支持 DML 并不支持 DDL,其创建常用 DDL 语法如下:
mysql> SET GLOBAL
log_bin_trust_function_creators =
ON
;#在配置好后在my.cnf或my.ini中添加
Query OK,0 rows
affected (0.00 sec)
CREATE TRIGGER 触发器名
AFTER/BEFORE INSERT/UPDATE/DELETE ON 表名 FOR
EACH ROW #这句话在MYSQL是固定的
BEGINDML 语句;
END;
此外还要注意在 MySQL 的触发器下的 old 于 new 的区别,以下引用网络上一段图文:
1,当使用 insert 语句的时候,如果原表中没有数据的话,那么对于插入数据后表来说新插入的那条数据就是 new
2,当使用 delete 语句的时候,删除的那一条数据相对于删除数据后表的数据来说就是 old
3,当使用 update 语句的时候,当修改原表数据的时候相对于修改数据后表的数据来说原表中修改的那条数据就是 old,而修改数据后表被修改的那条数据就是 new
所以在触发器中使用 new 和 old 还是有区别的,在这里用 2 张表说明下,有 2 张表 tbl_name 和 tbl_name_log
tbl_name 的 DDL:
tbl_name_log 的 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 中分别对 insert/update/delete 三种事件后做出记录于 tbl_name_log 表中,就可以在 tbl_name 添加 3 个触发器:
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 表进行插入,更新,删除后都会有相应的记录于 tbl_name_log 表中.
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 ;
来源: http://www.linuxidc.com/Linux/2018-01/150220.htm