MySQL数据库8（二十七）触发器

触发器

触发器概念

基本概念

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

 

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

 

作用

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

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

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

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

 

 

触发器优缺点

优点

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

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

 

缺点

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

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

 

 

触发器基本语法

创建触发器

基本语法

create trigger 触发器名字 触发时机 触发时间 on 表 for each row

begin

 

end

 

触发对象：on 表 for each row ，触发器绑定实质是表中的所有行，因此当每一行发生指定的改变的时候，就会触发触发器。

 

触发时机

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

 

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

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

 

触发事件

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

 

insert：插入操作

update：更新操作

delete：删除操作

 

注意事项：

一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个：一张表中只能有一个对应after insert 触发器

 

因此，一张表中最多的触发器只能有6个：before insert，before update，before delete，after insert，after update，after delete

 

查看触发器

1、查看全部触发器

show triggers；

2、查看触发器创建语句

show create trigger 触发器名字；

 

触发触发器

让触发器指定的表中，对应的时机发生对应的操作即可。

 

删除触发器

基本语法：drop trigger 触发器名字；

 

触发器应用

记录关键字：new 、old

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

 

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

基本语法：关键字.字段名

 

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

insert：插入之前为空，没有old

delete：清空数据，没有new

 

ps:rigger和function中不能出现select * from table形式的查询，因为其会返回一个结果集；而这在mysql的trigger和function中是不可接受的，但是在存储过程中可以。在trigger和function中可以使用select ... into ...形式的查询。

商品自动扣除库存

需求：有两张表，一张是商品表，一张是订单表（订单中会保留商品id），每次订单生成，商品表中对应的库存就应该发生变化。

1、创建两张表：商品表，订单表

2、创建触发器：如果订单表发生数据插入，对应的商品就应该减少库存

create trigger 名字 after insert on my_orders for each row

3、触发触发器

-- 创建两张表
create table my_goods(
id int primary key auto_increment,
name varchar(20) not null,
inv int
)charset utf8;

create table my_orders(
id int primary key auto_increment,
goods_id int not null,
goods_num int not null
)charset utf8;

insert into my_goods values(null,'手机',1000),(null,'电脑',5000),(null,'平板',100);

-- 自动扣除商品库存的触发器
delimiter $$
create trigger a_i_o_t after insert on my_orders for each row
begin
    -- 更新商品库存：new 代表新增的订单
    update my_goods set inv = inv - new.goods_num where id = new.goods_id;
end
$$
delimiter ;

 -- 触发触发器
insert into my_orders values(null,3,5);

 

完善：如果库存数量没有商品订单多怎么办？

操作目标：订单表，操作时机：下单前；操作事件：插入事件

-- 判断库存
delimiter $$
create trigger b_i_o_t before insert on my_orders for each row
begin 
    -- 取出库存数据进行判断
    select inv from my_goods where id = new.goods_id into @inv;

    -- 判断
    if @inv < new.goods_num then
        -- 中断操作：暴力解决，主动出错
        insert into XXX values('XXX');
    end if;
end
$$
delimiter ;

insert into my_orders values(null,3,100);

触发器一旦出错，就会撤销之前操作。