SQL trigger

1. What is a trigger

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程。

它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

2.How to create a trigger

(1).创建四要素

• 监视地点（table）
• 监视事件（update、insert、delete）
• 触发时间（before、after）
  • before用于校验，after用于补充
• 触发事件（update、insert、delete）

(2).语法

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

3.Example

use dailyExercise;

#商品表
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);

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

#写死的触发器
CREATE trigger change_product_num 
after INSERT 
on orders
FOR EACH ROW
BEGIN 
    UPDATE product set num = num - 2 WHERE id=1;
END 

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

#引用插入行的值: 用new代表新插入的行，行中的具体值用new.列名进行表示
CREATE trigger change_product_num_new 
after INSERT 
on orders
FOR EACH ROW
BEGIN 
    UPDATE product set num = num - new.amount WHERE id=new.product_id;
END 

insert into orders(product_id, amount) values(3,5);

#前面监听了insert，引用新数据用new
#接下来监听delete，引用旧数据用old

CREATE trigger change_product_num_old
after DELETE 
on orders
FOR EACH ROW
BEGIN 
    UPDATE product set num = num + old.amount WHERE id=old.product_id;
END 

delete from orders where orders_id=1;

#前面监听了insert与delete，接下来监听update
#更新前的数据为old，更新后的数据为new

CREATE trigger change_product_num_old_new
after UPDATE  
on orders
FOR EACH ROW
BEGIN 
    UPDATE product set num = num + old.amount - new.amount WHERE id=old.product_id;
END 

update orders set amount = 3 WHERE orders_id = 5;