sql server update触发器_SQL高级特性——触发器

一、什么是触发器

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

二、触发器的创建

1.创建四要素

• 监视地点（table）
• 监视事件（update、insert、delete）
• 触发时间（before、after）
• 触发事件（update、insert、delete）

2.语法

CREATE TRIGGER <触发器名> <监视时间> <监视事件>
ON 监视地点 FOR EACH ROW 
begin
    触发事件
end;

三、案例解析

1.建表

#商品表
create table product
(
　　id int primary key auto_increment,
　　name varchar(20),
　　num int
);

#订单表
create table orders
(
　　orders_id int primary key auto_increment,
 　 product_id int,
    amount int
);
insert into product(name,num) values('商品1',10),('商品2',10),('商品3',10);

商品表（商品id，商品名称，商品数量）

订单表（订单id，商品id，购买数量）

2.构造触发器

问题一：有人下单了2件'商品1'，那么如何构建触发器？

问题分析：首先往订单表中插入数据（1,2），然后商品表中对应的商品数量num = num -2

如果我们不用触发器则会写下面两条sql语句：

insert into order(product_id, amount) values(1,2);
update product set num = num - 2 where id = 1;

下面我们构建触发器：

delimiter $
create trigger trig_1 after insert on orders
for each row
begin 
  set num = num - 2 where id = 1;
end$;

delimiter $表示将$作为sql语句的结束标志，因为在begin...end之间的sql语句用分号结尾，如果end后面再用分号就会报错。

接下来我们再执行下面语句后就会发现product表中商品1的数目减少了2。

insert into orders(product_id, amount) values(1,2);

问题二：上面的例子我们把触发事件写死了，意思就是如何下的订单是别的商品我们依然会减少商品1的数量。如果这时候又来一个订单，有人买了2件商品2，那么对于这种情况如何灵活的书写触发器呢？

分析：这个问题的关键就在于我们要能够去引用插入的行的值。用new代表新插入的行，行中的具体值用new.列名进行表示。

那么我们就可以这么写：

create trigger trig_2 after insert on orders
for each row 
begin
  update product set num = num - new.amount where id = new.product_id;
end$

问题三：当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

问题分析：用户撤销了一个订单，即执行了delete操作，删除了一行，引用这一行用的是old，使用具体的列名和上面方法一样，使用old.列名。

那么触发器写法如下：

create trigger trig_3 after delete on orders
for each row
begin
  update product set num = num + old.amount where id = old.product_id
end$

问题四：当用户修改一个订单的数量时，我们触发器修改怎么写?

问题分析：修改订单相当于执行了update语句，对于update来说，更新前的行用old表示，更新后的行用new表示。所以触发器写法如下：

create trigger trig_4 after update on orders
for each row
begin
  update product set num = num + old.amount - new.amount where id = old.product_id
end$