sql之触发器trigger（3）

水深无声 2022-05-13 10:44 196阅读 0赞

**知识点：**

1.  什么是触发器
2.  创建、撤销、查看触发器
3.  触发器的作用（应用场景）
4.  触发器和储存过程的区别
5.  注意：  
    1.MySQL中，一个表在相同触发时间的相同触发事件只能创建一个触发器。如触发事件insert，触发时间为after的触发器只有一个。  
    2.MySQL中，触发器执行的顺序是before触发器、表操作(insert、update和delete)和after触发器
    
        create trigger before_insert 
        before insert on department 
        for each row 
        insert into trigger_test values(null, "before_insert");
        
        
        create trigger after_insert 
        before insert on department 
        for each row 
        insert into trigger_test values(null, "after_insert");
    
    3.激活触发器时，对触发器的执行语句存在一些限制。如，触发器中不能包含start transaction、commit或rollback等关键词也不能包含call语句。  
    4.在触发器执行过程中，任何步骤出错都会阻止程序向下执行，对于普通表来说，已经更新过的记录不能回滚，更新后的数据将继续保留在表中。

**一.触发器**

一个对象。  
触发器的作用是在执行insert、delete和update命令之前或之后自动调用sql命令或存储过程。

*  特定事件：增、删、改
 *  该对象：增、删、改

它包含四个要素：

1.  监视地点（table）
2.  监视事件（insert/update/delete）
3.  触发时间（after/before）
4.  触发事件（insert/update/delete）

**二.创建、撤销触发器**

1.创建触发器

语法：
    create trigger trigger_name
    after/before   insert/update/delete on tb_name
    for each row
    begin
    sql 语句;（触发的语句一句或者多句）
    end;
    
    解析：
    trigger_name：触发器的名称
    tirgger_time：触发时机，为BEFORE或者AFTER,before和after参数指定触发器执行的时间;
    trigger_event：触发事件，为INSERT、DELETE或者UPDATE
    tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
    for each row表示任何一条记录上的操作满足触发事件都会触发该触发器
    
    注意：
    1.其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开；
    2.一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突
    为解决此问题可用DELIMITER，如：DELIMITER$$，可以将结束符号变成$$
    当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;
    3.在BEGIN...END语句中也可以定义变量，但是只能在BEGIN...END内部使用：
    DECLARE var_name var_type [DEFAULT value] #定义变量，可指定默认值
    SET var_name = value  #给变量赋值
    
    
    例子：
    第一步：创建商品表goods
    create table goods(
    goods_id int(10) not null auto_increment，
    goods_name varchar(255) not null,
    goods_num int(10) not null,
    primary key(goods_id)
    )engine=innodb auto_increment=1 default charset=utf8
    
    第二步：创建订单表orders
    create table orders(
    order_id int(10) not null auto_increment,
    goods_id int(10) not null,
    order_num int(10) not null,
    primary key(order_id),
    foreign key(goods_id) references goods(goods_id)
    )engine=innodb auto_increment=1 default charset=utf8
    
    第三步：在goods表、orders表中各插入4条记录
    INSERT INTO  goods VALUE(NULL,'笔记本',20);
    INSERT INTO  goods VALUE(NULL,'手机',20);
    INSERT INTO  goods VALUE(NULL,'鼠标',20);
    INSERT INTO  goods VALUE(NULL,'键盘',20);
    
    INSERT INTO  orders VALUE(NULL,1,5);
    INSERT INTO  orders VALUE(NULL,4,5);
    INSERT INTO  orders VALUE(NULL,2,5);
    INSERT INTO  orders VALUE(NULL,3,5);
    
    第四步：创建触发器t1:实现购买任意商品，对应的商品数量相应的减少：
    分析：
    监视地点：orders 表 
    监视事件：insert操作
    触发时间：在insert操作之后
    触发事件：update操作
    
    delimiter$$  #将语句的分隔符改为$$
    create trigger t1
    after insert on orders
    for each row
    begin
    update goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id; #new和old的作用见下文
    end$$
    delimiter;    #将语句的分隔符改回原来的分号";"

new和old的使用：

注：对于insert 而言 新增的行使用new 来表示，行中的每一列的值用 new.列名来表示

![format_png][]

2.撤销触发器

语法：
    drop  trigger trigger_name;
    
    例：dorp  trigger t1;

3.查看触发器：指查看数据库中已存在的触发器的定义、状态和语法等信息。

select * from information_schema.triggers;
    
    select * from information_schema.triggers where trigger_name='触发器名';

**三.触发器的应用（基于创建触发器的例子练习）**  
1. 实现购买任意商品，对应的商品数量相应的减少

delimiter$$  #将语句的分隔符改为$$
    create trigger t1
    after insert on orders
    for each row
    begin
    update goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id; 
    end$$
    delimiter;    #将语句的分隔符改回原来的分号";"

新增一条订单：

insert into orders(goods_id,order_num) value(2,5);#插入成功后可以看到表goods的goods_id=2商品的goods_num减少了2

2.撤销订单（商品数量增加）：

delimiter$$  #将语句的分隔符改为$$
    create trigger t2
    after delete on orders
    for each row
    begin
    update goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id; 
    end$$
    delimiter;    #将语句的分隔符改回原来的分号";"

删除一条订单：

delete from orders where order_id=4;
    #这条sql语句执行之后，可以在goods表中看到order_id=4的对应的goods_id其goods_num增加了order_num

3.修改订单（包括修改购买的数量以及购买的商品）：

分析：
    分两步：
    1. 撤销订单；（delete）；//原商品数量增加
    2. 新增订单：（insert）；//新商品数量减少
    
    
    delimiter$$  #将语句的分隔符改为$$
    create trigger t3
    after update on orders
    for each row
    begin
    #撤销订单
    update goods set goods_num=goods_num+old.order_num where goods_id=old.goods_id;
    #新增订单
    update goods set goods_num=goods_num-new.order_num where goods_id=new.goods_id;
    end$$
    delimiter;    #将语句的分隔符改回原来的分号";"

**四.储存过程和触发器的区别**

触发器：它是一个特殊的存储过程，它是MySQL在insert、update、delete的时候执行，自动执行，不能直接调用。

**触发器原理：**

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发(激活)执行。  
触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。  
触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

**触发器的作用：**

触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对[数据库][Link 1]中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义操作消息，维护非规范化数据以及比较数据修改前后的状态。与CHECK约束不同，触发器可以引用其它表中的列。在下列情况下使用触发器实现复杂的引用完整性;强制数据间的完整性。创建多行触发器，当插入，更新、删除多行数据时，必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作，防止非法修改数据。

触发器与存储过程的区别：

触发器与存储过程的主要区别在于触发器的运行方式。  
存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行，同时还取决于触发器是怎样创建的，当UPDATE发生时使用一个更新触发器，当INSERT发生时使用一个插入触发器，当DELETE发生时使用一个删除触发器。

[format_png]: /images/20220513/45e3cdf771124c7aa9017b3aa25ac353.png
[Link 1]: https://www.2cto.com/database/