mysql--触发器复习

x33g5p2x  于11个月前 转载在 Mysql  
字(2.6k)|赞(0)|评价(0)|浏览(90)

触发器

触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行

触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。

作用

可在写入数据前,强制检验或者转换数据(保证护数据安全)

触发器发生错误时,前面用户已经执行成功的操作会被撤销,类似事务的回滚

触发器创建语法四要素

  • 监视地点(table)
  • 监视事件(insert/update/delete)
  • 触发时间(after/before)
  • 触发事件(insert/update/delete)

对于一张表来说,触发器就有6种操作(监视事件/*触发时间)

AFTER INSERT
AFTER UPDATE
AFTER DELETE
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE

当 SQL 指令发生时,会令行中数据发生变化,而每张表中对应的行有两种状态:数据操作前和操作后

  • before:表中数据发生改变前的状态
  • after:表中数据发生改变后的状态

PS:如果 before 触发器失败或者语句本身失败,将不执行 after 触发器(如果有的话)

注意事项

  • 在 MySQL 5 中,触发器名必须在每个表中唯一,但不是在每个数据库中唯一,即同一数据库中的两个表可能具有相同名字的触发器
  • 每个表的每个事件每次只允许一个触发器,因此,每个表最多支持 6 个触发器,before/after insert、before/after delete、before/after update

触发器的创建和使用

1.格式

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体,每行用分号结尾
end
自定义的结束符号

delimiter ;

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

2.示例

DELIMITER ##
#当我们向payment表中插入数据时,触发事件,向depart表中插入一条记录
CREATE TRIGGER pay_log AFTER INSERT ON payment FOR EACH ROW 
#触发器执行的逻辑
BEGIN

INSERT INTO depart (d_name) VALUES("新增一条记录");

END
##
DELIMITER ;
INSERT INTO payment(`serial`) VALUES("大忽悠");

1.查看全部触发器

语法:show triggers;

2.查看触发器的创建语句

语法:show create trigger 触发器名字;

Tabel: 监视的表

3.删除触发器

基本语法:drop trigger 触发器名字

触发器应用

触发器针对的是数据库中的每一行记录,每行数据在操作前后都会有一个对应的状态,触发器将没有操作之前的状态保存到 old 关键字中,将操作后的状态保存到 new 中

语法:old/new.字段名

需要注意的是,old 和 new 不是所有触发器都有

delimiter ##
-- 创建触发器
create trigger after_insert_order after insert on orders for each row
begin
    -- new 代表 orders 表中新增的数据
    update goods set goods_num = goods_num - new.goods_num where id = new.goods_id;
end
##
delimiter ;

PS:对于 auto_increment 列,new 在 insert 执行之前包括 0,在 insert 执行之后包括新的自动生成的值

这里我们可以根据新插入的 orders 表中的数据来修改 goods 表的库存,此时新插入的数据用 new 来表示

如果买 5 个 id 为 1 的商品,此时 id 为 1 的商品的库存得到正确的修改。当然,如果买其他种类的商品,最后得到的结果也是正确的,这里就不一一演示了

错误情况

当然我们还需要考虑一种情况:如果此时商品的库存不够了,该怎么处理?

delimiter ##
-- 创建触发器
create trigger before_insert_order before insert on orders for each row
begin
    -- 取出 goods 表中对应 id 的库存
    -- new 代表 orders 表中新增的数据
    select goods_num from goods where id = new.goods_id into @num;
    
    -- 用即将插入的 orders 表中的库存和 goods 表中的库存进行比较
    -- 如果库存不够,中断操作
    if @num < new.goods_num then
        -- 中断操作:暴力解决,主动出错
        insert into xxx values(xxx);
    end if;
end
##
delimiter ;

直接创建这个触发器

如果我们买 id 为 3 的商品 100 件,可以看到,此时报错,同时 orders 表和 goods 表的数据并没有得到更新

可以看到,数据连 orders 表都未能插入,那么肯定就不会执行 insert after 这个触发器了

同时,如果在触发器中出现错误,那么前面的已经执行的操作也会全部清空

注意事项

① mysql触发器不能对同一张表进行修改操作

假如我在 before update 的时候作一条更新语句,随便将里面哪个字段进行更新

delimiter //
create trigger up before update on orders for each row
begin
    update orders set goods_id = 10 where id = new.id;
end;
//
delimiter ;

触发器创建成功

接下来我用 update 语句对 orders 表进行更新

此时报错了,提示不能进行更新。之后,我又尝试在触发器中进行 insert 和 delete 操作,之后更新的时候还是报同样的错误

因此说明:MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作,否则会报错

总结

优点

  • 触发器可以通过数据库中的关联表实现级联更改,即一张表数据的改变会影响其他表的数据
  • 可以保证数据安全,并进行安全校验

缺点

  • 过分依赖触发器,影响数据库的结构,增加数据库的维护成本

参考文章

https://blog.csdn.net/babycan5/article/details/82789099

相关文章