MySQL8触发器

基本概念

作用

触发器优缺点

触发时机

触发事件

触发器基操（基本操作）

触发器应用

触发器的六种操作

案例

MySQL8触发器

基本概念

        触发器是一种特殊类型的存储过程，它不同于存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通够存储过程名字而被直接调用。

        触发器：trigger，是一种非常接近于js中的事件的知识，提前给某张表的所有记录绑定一段代码，如果该行的操作满足条件（触发），这段提前准备好的代码就会自动执行。

作用

        1、可在写入数据表之前，强制检验或者转换数据（保证数据安全）

        2、触发器发生错误时，异动的结果会被撤销（如果触发器执行错误，那么前面用户已经执行成功的操作也会被撤销：事务安全）

        3、部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器

        4、可依照特定的情况，替换异动的指令（instead of）（mysql不支持）

触发器优缺点

  优点：

        1、触发器可通过数据库中的相关表实现级联更改。（如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作（用户不知道））

2、保证数据安全，进行安全校验

  缺点：

        1、对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度

2、造成数据在程序层面不可控。

触发时机

   触发时机：

        每张表中对应的行都会有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态，数据操作前和操作后

        before：在表中数据发生改变前的状态

        after：在表中数据已经发生改变后的状态

触发事件

        触发事件：mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）

        insert：插入操作

        update：更新操作

        delete：删除操作

   注意事项：

               一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个：一张表中只能有一个对应after insert 触发器，因此，一张表中最多的触发器只能有6个：before insert，before update，before delete，after insert，after update，after delete

触发器基操（基本操作）

1，查看全部触发器

show triggersG;

2，查看触发器创建语句

show create trigger 触发器名字;

3，删除触发器

drop trigger 触发器名字;

4，创建触发器

DELIMITER //
CREATE TRIGGER [触发器的名字]
[触发器执行时机] [触发器监测的对象]
ON [表名]
FOR EACH ROW [触发器主体代码]//
DELIMITER ;
​
# DELIMITER //：MySQL 默认分隔符是; 但在触发器中，我们使用 // 表示触发器的开始与结束。
# [触发器的名字]：这里填写触发器的名字
# [触发器执行时机]：这里设置触发器是在关键动作执行之前触发，还是执行之后触发。
# [触发器监测的对象]：触发器可以监测 INSERT、UPDATE、DELETE 的操作，当监测的命令对触发器关联的表进行操作时，触发器就被激活了。
# [表名]：将这个触发器与数据库中的表进行关联，触发器定义在表上，也附着在表上，如果这个表被删除了，那么这个触发器也随之被删除。
# FOR EACH ROW：这句表示只要满足触发器触发条件，触发器都会被执行，也就是说带上这个参数后，触发器将监测每一行对关联表操作的代码，一旦符合条件，触发器就会被触发。
# [触发器主体代码]：这里是当满足触发条件后，被触发执行的代码主体。这里可以是一句 SQL 语句，也可以是多行命令。如果是多行命令，那么这些命令要写在 BEGIN...END 之间。

触发器应用

        记录关键字：

                new 、old

                触发器针对的是数据表中的每条记录（每行），每行在数据操作前后都有一个对应的状态，触发器在执行之前就将对应的状态获取到了，将没有操作之前的状态（数据）都保存到old关键字中，而操作后的状态都放到new中

        在触发器中，可以通过old和new关键字来获取绑定表中对应的记录数据

基本语法：

        关键字.字段名

        old和new并不是所有的触发器都有

        insert：插入之前为空，没有old delete：清空数据，没有new

触发器的六种操作

BEFORE INSERT ： 在插入数据前，检测插入数据是否符合业务逻辑，如不符合返回错误信息。
AFTER INSERT ： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。
BEFORE UPDATE ：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息。
AFTER UPDATE ：在更新数据后，将操作行为记录在 log 中
BEFORE DELETE ：在删除数据前，检查是否有关联数据，如有，停止删除操作。
AFTER DELETE ：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息。

案例

#创建触发器 要有权限
# 例如 我们开了个银行，需要记录客户的数据，根据一次性存入的余额判断该客户是不是vip用户，制作四张表，一张客户信息表，一张客户余额表，一张记录创建时间的表，再来一张记录更新操作的表
# 重置自增列从ALTER TABLE creation_time AUTO_INCREMENT = 1;
# TRUNCATE customer_information; 清空表内容，不会记录日志

# 第一张 客户信息表（customer_information）
CREATE TABLE customer_information (
    customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
    customer_name VARCHAR ( 20 ) NOT NULL COMMENT '客户名称',
    customer_age TINYINT UNSIGNED NOT NULL COMMENT '客户年龄',
customer_rank VARCHAR ( 20 ) NOT NULL COMMENT '客户级别' 
);
# 第二章表 客户余额表(customer_amount)
CREATE TABLE customer_amount (
    customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
    customer_name VARCHAR ( 20 ) NOT NULL COMMENT '客户名称',
customer_balance FLOAT NOT NULL DEFAULT 0 COMMENT '客户余额' 
);
# 第三张 创建用户记录表
CREATE TABLE creation_time(
customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
customer_name VARCHAR ( 20 ) NOT NULL COMMENT '客户名称',
customer_status  VARCHAR ( 20 ) NOT NULL COMMENT '客户状态',
customer_date DATETIME  NOT NULL COMMENT '创建时间'
);
# 第四张 记录更新删除操作的表（new_update）
CREATE TABLE new_update (
customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '客户ID',
customer_name VARCHAR ( 20 ) NOT NULL COMMENT '客户名称',
customer_balance_old FLOAT NOT NULL DEFAULT 0 COMMENT '记录余额变化前',
customer_balance_new FLOAT NOT NULL DEFAULT 0 COMMENT '记录余额变化后',
operate_user VARCHAR(20) NOT NULL DEFAULT '记录操作用户',
update_date DATETIME  NOT NULL COMMENT '记录操作时间',
del_date varchar(20) DEFAULT "正常" COMMENT '客户状态'
);
​
# 首先创建 BEFORE INSERT 触发器
# 此触发器用来记录客户的创建时间等
DELIMITER //
create trigger creation_found_date 
before insert on customer_information 
for each row 
insert into creation_timon_time values(new.customer_id,new.customer_name,'成功',now()); //
DELIMITER ;
​

# 创建一个判断用户年龄的触发器，判断用户的年龄小于100的才能创建
DELIMITER //
create trigger customer_age 
before insert on customer_information 
for each row 
if new.customer_age>100 then signal sqlstate '66666' set message_text="您的年龄有误写入失败！请填写年龄小于100岁的用户！"; end if //
DELIMITER ;
# signal sqlstate 使用语句在存储的程序（例如存储过程，存储函数，触发器或事件）中向调用者返回错误或警告条件。语句提供了对返回值(如值和消息)的信息的控制
# message_text 你要返回的内容
​
# 触发器 AFTER INSERT
# 当用户的数据插入成功以后在写入到账户余额表中
DELIMITER //
create trigger create_amount
after insert on customer_information 
for each row 
insert into customer_amount values(new.customer_id,new.customer_name,0);//
DELIMITER ;
​
# 无E烦从网上办卡了，没激活就没存钱
INSERT INTO customer_information(customer_name,customer_age,customer_rank) VALUES('无E烦',24,'普通用户');
# 查看触发器是否启动，查看两张表内容
SELECT * FROM customer_information;
SELECT * FROM customer_amount;
SELECT * FROM creation_time;
​
# BEFORE UPDATE 在更新之前操作
# 这个 无E烦 用户有一天彩票中了一百万，来我们银行存钱了，那么我们银行有规矩一次性存入一百万就是我们的VIP用户，根据这个写一个触发器
DELIMITER //
create trigger save_money 
before update on customer_amount 
for each row if new.customer_balance>=1000000 and new.customer_name=old.customer_name then 
update customer_information set customer_rank='VIP' where customer_id=old.customer_id;end if//
DELIMITER ;
# 我们将存款改到数据库内
update customer_amount set customer_balance=1000000 where customer_name='无E烦';
# 再次查看用户有没有升级称为VIP用户
SELECT * FROM customer_information;
SELECT * FROM customer_amount;
​
# AFTER UPDATE 触发器 
# 用户来存钱了，然后我们修改用户余额，那么需要记录一下，出来问题可以找对应的人
DELIMITER //
create trigger record_operation after update on customer_amount for each row insert into new_update(customer_name,customer_balance_old,customer_balance_new,operate_user,update_date) values(old.customer_name,old.customer_balance,new.customer_balance,(SELECT USER()),now());//
DELIMITER ;
# 无E烦又来了，这次又带来十万块钱 存入到我们银行当中 
update customer_amount set customer_balance=1100000 where customer_name='无E烦';
# 查看更新后是否又记录
SELECT * FROM new_update;
SELECT * FROM customer_amount;
​
​
# BEFORE DELETE 删除前做操作
# 如果有一天，有个潜入我行系统的“小波崽子”，删除可以看到我们的用户信息表，想要删除我们的客户信息中某一个人的信息，那么我们就要针对客户信息的表做一个防删除的触发器
DELIMITER //
create trigger del_customer 
before delete on customer_information 
for each row if old.customer_name in (select customer_name from customer_amount ) and (select customer_balance from customer_amount where customer_name=old.customer_name)>0 then 
signal sqlstate '66666' set message_text = '这位客户还有剩余额度！无法删除！'; 
end if //
DELIMITER ;
# 测试删除
delete from customer_information where customer_id=1;
​
# AFTER DELETE 删除后操作
# 无E烦 又来我们银行取钱了，打算全部取走并注销账户，针对这个来写一个触发器，一旦余额为0的时候就可以销户了，需要联动删除，连着客户信息一起消除，并记录操作
DELIMITER //
create trigger del_user
after delete on customer_amount 
for each row 
if old.customer_balance=0 then 
delete from customer_information where customer_name=old.customer_name; 
update creation_time set customer_status='已销户',customer_date=NOW(); 
insert into new_update(customer_name,customer_balance_old,customer_balance_new,operate_user,update_date,del_date) values(old.customer_name,0,0,(select user()),now(),'已销户'); 
end if//
DELIMITER ;
# 取走钱并销户
update customer_amount set customer_balance=0;
delete from customer_amount where customer_name="无E烦";
# 查看四张表内容
SELECT * FROM customer_information;
SELECT * FROM customer_amount;
SELECT * FROM creation_time;
SELECT * FROM new_update;
​
​​
# if elseif 语句 
DELIMITER //
create trigger customer_age 
before insert on customer_information 
for each row 
if new.customer_age>100 then 
signal sqlstate '66666' set message_text="您的年龄有误写入失败！请填写年龄小于100岁的用户！"; elseif new.customer_age <=17 then 
signal sqlstate '66666' set message_text="您的年龄有误写入失败！请填写年龄大于等于18岁的用户！"; 
end if//
DELIMITER ;
# if else 语句
DELIMITER //   # 更改结束符
create trigger save_money  # 创建触发器 名字为save_money  
before update on customer_amount  # 更新之前对customer_amount表操作
for each row   # 每一行只要满足就触发
if new.customer_balance>=1000000 then  # 判断新改的值如果大于100w就改为VIP客户
update customer_information set customer_rank='VIP' where customer_name=old.customer_name; 
else  # 否则就改为普通用户
update cusstomer_information set customer_rank='普通用户' where customer_name=old.customer_name;
end if//
DELIMITER ;

最后

以上就是呆萌西装为你收集整理的MySQL8 触发器（实现多表联动效果）MySQL8触发器的全部内容，希望文章能够帮你解决MySQL8 触发器（实现多表联动效果）MySQL8触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。