一、介绍

1、触发器是一种特殊的存储过程。触发器和存储过程一样,是一个能够完成特定功能、存储在数据库服务器上的SQL片段,但是触发器无语调用,当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行,无需手动调用。

2、在MySql中,只有执行insert,delete,update操作时才能触发触发器的执行

3、触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作

4、使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在触发器只支持行级触发,不支持语句级触发

 二、操作

1、表数据准备

# 用户表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


# 用户操作日志表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_log
-- ----------------------------
DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

 

2、触发器格式

# 创建只有一个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;

# 创建有多个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin  
   执行语句列表
end;

 3、操作

drop TRIGGER if EXISTS TRIGGER_test;
-- 需求1:当users表添加一行数据,则会自动在user_log添加日志记录
delimiter $$
CREATE TRIGGER TRIGGER_test after INSERT
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES('添加了一条数据',NOW());
end $$
delimiter ;

INSERT INTO users(user_name,sex) VALUES('xiaohemaio','');


-- 需求2:当users表修改一行数据,则会自动在user_log添加日志记录
drop TRIGGER if EXISTS TRIGGER_test1;

delimiter $$
CREATE TRIGGER TRIGGER_test1  BEFORE UPDATE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES('修改了一条数据',NOW());
end $$
delimiter ;

update users set user_name='迪丽热巴' WHERE id=3;

三、触发器NEW和OLD的使用

MySql中定义了NEW和OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容。

使用方法:NEW.columnName (columnName为相应数据表某一列名)

1、案例

 

-- 案例一
drop TRIGGER if EXISTS TRIGGER_test2;

delimiter $$
CREATE TRIGGER TRIGGER_test2 after INSERT
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('添加的用户信息为:',NEW.user_name,' 性别为:',NEW.sex ),NOW());
end $$
delimiter ;

INSERT INTO users(user_name,sex) VALUES('xiaohemaio','');


-- 案例二 
drop TRIGGER if EXISTS TRIGGER_test3;

delimiter $$
CREATE TRIGGER TRIGGER_test3  BEFORE UPDATE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('将:',OLD.user_name,' 修改为:',NEW.user_name ),NOW());
end $$
delimiter ;

update users set user_name='迪丽热巴' WHERE id=4;

-- 案例三
drop TRIGGER if EXISTS TRIGGER_test4;

delimiter $$
CREATE TRIGGER TRIGGER_test4  BEFORE DELETE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('将id为:',OLD.user_name,' 已删除' ),NOW());
end $$
delimiter ;


DELETE FROM  users WHERE id=4;

 四、其他操作

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
drop TRIGGER if EXISTS 触发器名;

 五、注意事项

1、触发器中不能对本表进行insert,update,delete操作,以免递归循环触发

2、尽量少使用 触发器,假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么这个insert 的效率就非常低了。

3、触发器是针对每一行的数据,对增删改非常频繁的表上切记不要使用触发器,因为非常消耗资源。

作者:|SportSky|,原文链接: https://www.cnblogs.com/sportsky/p/16315892.html

文章推荐

docker服务部署、迁移与备份、dockerfile、私有仓库

Redis概述及基本数据结构

ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实...

『忘了再学』Shell基础 — 19、使用declare命令声明变量类型

Sentinel流控规则可以这样玩?

K8s Calico网络插件

关于区间操作查找(前缀和与差分)

什么是请求参数、表单参数、url参数、header参数、Cookie参...

明天 520,说说两性之间的那些事

大神方案|如何重写一个万行代码的类文件

一个简单易用的文件上传方案

MSDN系统原版镜像全系列所有历史版本下载