参考:https://zhuanlan.zhihu.com/p/439273702
触发器(Trigger)是 MySQL 中非常实用的一个功能,它可以在操作者对表进行「增删改」 之前(或之后)被触发,自动执行一段事先写好的 SQL 代码。
有时候,你需要设置在INSERT 表之前检测操作者是否输入错误数据、在 UPDATE 时,记录操作者的行为 log ,以及在DELETE 时,判断删除的信息是否符合删除规则。 这三类操作都可以使用 MySQL 触发器来实现。
1、六种触发器
BEFORE INSERT : 在插入数据前,检测插入数据是否符合业务逻辑,如不符合返回错误信息。
AFTER INSERT : 在表 A 操作insert后,会触发关联的业务逻辑到 表B 中。
BEFORE UPDATE :在更新数据前,检测更新数据是否符合业务逻辑,如不符合返回错误信息。
AFTER UPDATE :在更新数据后,会触发关联的业务逻辑到 表B 中,例如将操作行为记录在 log 中
BEFORE DELETE :在删除数据前,检查是否有关联数据,如有,停止删除操作。
AFTER DELETE :删除表 A 信息后,会触发关联的业务逻辑到 表B 中。
2、使用六种触发器
(1)准备,创建表和插入数据
create table customers(
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(50),
level VARCHAR(50)
) ENGINE=INNODB;
Insert into customers (customer_id, customer_name, level )values('1','Jack Ma','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','Robin Li','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','Pony Ma','VIP');
创建另一个表customer_status,用于保存 customers 表中客户的备注信息
Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;
再创建一个 sales 表,这个表与 customer_id 关联。保存与客户有关的销售数据
Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;
我们再建一个 audit_log 表
Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;
(2)触发器的基本语法
DELIMITER //
CREATE TRIGGER [触发器的名字]
[触发器执行时机] [触发器监测的对象]
ON [表名]
FOR EACH ROW [触发器主体代码]//
DELIMITER ;
触发器的结构包括:
- DELIMITER //:MySQL 默认分隔符是; 但在触发器中,我们使用 // 表示触发器的开始与结束。
- [触发器的名字]:这里填写触发器的名字
- [触发器执行时机]:这里设置触发器是在关键动作执行之前触发,还是执行之后触发。
- [触发器监测的对象]:触发器可以监测 INSERT、UPDATE、DELETE 的操作,当监测的命令对触发器关联的表进行操作时,触发器就被激活了。
- [表名]:将这个触发器与数据库中的表进行关联,触发器定义在表上,也附着在表上,如果这个表被删除了,那么这个触发器也随之被删除。
- FOR EACH ROW:这句表示只要满足触发器触发条件,触发器都会被执行,也就是说带上这个参数后,触发器将监测每一行对关联表操作的代码,一旦符合条件,触发器就会被触发。
- [触发器主体代码]:这里是当满足触发条件后,被触发执行的代码主体。这里可以是一句 SQL 语句,也可以是多行命令。如果是多行命令,那么这些命令要写在 BEGIN...END 之间。
注:在创建触发器主体时,还可以使用OLD和NEW 来获取 SQL 执行INSERT,UPDATE和DELETE 操作前后的写入数据。这里没看明白没关系,我们将会在接下来的实践中,展开讲解。
2.1 BEFORE INSERT触发器使用方法
DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你输入的销售总额超过 10000 元。";
END IF//
DELIMITER ;
上面这段代码中,我们使用IF...THEN...END IF 来创建一个监测 INSERT 语句写入的值是否在限定的范围内的触发器。
这个触发器的功能时监测 INSERT 在写入sales_amount 值时,这个新增的(NEW)值是否符合条件( > 10000)。
当操作员录入一个超过 10000 的数字,会返回如下错误信息:
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '你输入的销售总额超过 10000 元。';
我们向 sales_amount 中插入一条 11000 的值。
我们 insert 一个值小于 10000 的数字:
Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7700');
输入值为 7700 小于设定的 10000 ,insert 命令执行成功。
2.2AFTER INSERT触发器使用方法
DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, '账户创建成功')//
DELIMITER ;
我们 INSERT 一条信息,看看触发器是否已启用:
Insert into customers (customer_id, customer_name, level )values('4','Xing Wang','VIP');
2.3BEFORE UPDATE触发器使用方法
BEFORE UPDATE触发器与BEFORE INSERT 触发器非常类似,我们可以使用BEFORE UPDATE 触发器在更新数据之前,先做一次业务逻辑检测,避免发生误操作。
我们来创建这个触发器:
DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP 级别客户不能降级为普通级别客户';
END IF //
DELIMITER ;
我们可以使用 OLD 来获取执行 UPDATE 命令前,客户的 level 值。同样,我们使用该IF...THEN...END IF语句来对 level 值是否符合规则进行判断。
2.4AFTER INSERT触发器使用方法
AFTER UPDATE 多用于 log 记录,在管理系统多操作者使用的环境中,管理员需要设置操作 log 记录,以便在出问题时,可以查看操作者对表编辑的操作,可追根溯源。
我们先来创建一个对 sales 表操作的 log 记录触发器。
当操作者对 sales 表进行修改后,操作记录会被写入 audit_log 表中。
触发器将监测用户 ID 、更新前的销售总额、更新后的销售总额、操作者 ID、修改时间等信息,作为 log 存入 audit_log 表中。
使用以下命令建立这个 log 记录触发器:x
DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;
当操作者对 sales 表中的一条客户信息进行 UPDATE 操作时,触发器会在UPDATE操作之后,将操作行为记录在 audit_log 中。包括 sales_id ,修改 sales_amount 值的前后变化。
销售总额的变化是审计的关键数据,所以要把它记录在 audit_log 中。使用OLD 来获取更新前的 sales_amount 值,使用 NEW 来获取更新后的值。
另外我们还要记录修改 sales 表的操作者信息及操作时间。
你可以使用 SELECT USER() 来检测当前操作用户的账号,用 NOW() 语句抓去当前服务器日期和时间。
2.5BEFORE DELETE触发器使用方法
BEFORE DELETE触发器会在DELETE语句执行之前调用。
这些类型的触发器通常用于在不同的相关表上强制执行参照完整性。
BEFORE DELETE 的应用场景通常是确保有关联的数据不被错误的误删除掉。
例如:sales 表通过customer_id 与customers表相关联。如果操作者删除了customers 表中的一条数据,那么 sales 表中某些数据就失去了关联线索。
为了避免这种情况的发生,我们需要创建一个 BEFORE DELETE触发器,防止记录被误删除。
DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '这位客户有相关联的销售记录,不能删除。';
END IF//
DELIMITER ;
2.6AFTER DELETE触发器使用方法
这个触发器在实际场景用的应用也比较广泛。比如银行系统中的升级降级操作,当客户花掉自己的账户积分后,激活触发器,触发器可以判断剩余积分是否满足客户当前等级,如果不满足,自动做降级操作。
AFTER DELETE触发器的另一个用途是在删除主表中的数据后,与这个主表关联的数据,一起自动删除。
我们来看一下这个触发器如何创建:
DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;
3.常用命令
(1)查看触发器
SHOW TRIGGERS;
(2)查找触发器的信息
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名称';
(3)删除触发器
Drop trigger [触发器名称];
标签:customer,触发器,log,六种,sales,DELIMITER,mysql,id
From: https://www.cnblogs.com/cgy1995/p/17138587.html