mysql触发器

mysql触发器

#触发器 TRIGGER

/*
触发器可以监视增删改
触发器可以触发增删改
触发器中有new和old这2个关键字，可以把new理解成新行，可以把old理解成旧行
注意：
INSERT触发器 只能使用new
DELETE触发器 只能使用old
UPDATE触发器  既可以使用new又可以使用old
*/

/*
触发器4要素
1.监视哪张表(table)
2.监视事件，即监视某表的哪些事件(事件包括INSERT/DELETE/UPDATE)
3.触发时间after/before
4.触发事件(事件包括INSERT/DELETE/UPDATE),触发事件即触发的时候要干的事情
*/

/*
注意：oracle数据库中有行级触发器和语句级触发器，但是mysql数
据库中，不支持语句级触发器,只支持行级触发器，所以必须写for each row

在oracle数据库中，行级触发器: 每更新表中的一条记录, 都会导致触发器执行(比如，一个update语句影响了5行记录，那
么触发器就会执行5次)
语句级触发器: 一个update/delete/insert语句只使触发器执行一次(比如，一个update语句无论影响了几行记录，触
发器都只执行1次)

在oracle数据库中，不写FOR EACH ROW就表示是语句级触发器，写FOR EACH ROW就表示是行级触发器
在oracle数据库中，FOR EACH ROW如果不写，无论update语句一次影响了多少行记录，触发器只执行1次

mysql数据库没有oracle数据库那么强大，在mysql数据库中，不支持语句级触发器，只支
持行级触发器，所以语法中必须写上FOR EACH ROW，如果不写上FOR EACH ROW就会报语法错误

创建触发器的语法
CREATE TRIGGER 触发器名字
AFTER/BEFORE (触发时间)
INSERT/UPDATE/DELETE (监视事件)
on 表名(监视地址即监视哪张表)
FOR EACH ROW (mysql不支持语句级触发器,所以必须写for each row)
BEGIN
sql1;
sql2;
......
sqlN;
END
*/

/*
案例

商品表 goods
订单表 ord

需求：当下1个订单时，商品库存相应减少(买几个商品，库存量就减少几个)
分析：
监视谁:  ord订单表
监视事件/监视动作: insert
触发时间： 这里我们暂时选择after
触发事件： update
*/

#商品表
CREATE TABLE goods(
gid int,
gname VARCHAR(50),
num int
)
#插入数据
INSERT INTO goods VALUES(1, 'ipad', 600);
INSERT INTO goods VALUES(2, '笔记本电脑', 500);
INSERT INTO goods VALUES(3, '牙膏', 200);
INSERT INTO goods VALUES(4, '六神花露水', 800);
#
SELECT * from goods;

#订单表
CREATE TABLE ord(
oid INT,
gid int,
num int
)

DELIMITER $
#创建触发器(下订单)
CREATE TRIGGER tri_ord_insert
AFTER
INSERT
ON ord
FOR EACH ROW
BEGIN
UPDATE goods SET num = num - new.num WHERE gid = new.gid;
END$

#插入数据
INSERT INTO ord VALUES(10, 1, 5);
INSERT INTO ord VALUES(11, 2, 3);
INSERT INTO ord VALUES(12, 3, 8);
INSERT INTO ord VALUES(13, 4, 20);
#
SELECT * FROM ord;
#
SELECT * FROM goods;

#查询已有触发器
SHOW TRIGGERS;
#删除触发器
#DROP TRIGGER 触发器名
DROP TRIGGER tri_ord_insert;
#

DELIMITER $
#创建触发器(删除订单)
CREATE TRIGGER tri_ord_delete
AFTER
DELETE
ON ord
FOR EACH ROW
BEGIN
UPDATE goods SET num = num + old.num WHERE gid = old.gid;
END$

#
DELETE FROM ord WHERE oid = 13;
#
SELECT * FROM ord;
#
SELECT * FROM goods;

DELIMITER $
#创建触发器(修改订单，修改订单的商品数量)
CREATE TRIGGER tri_ord_update
BEFORE
UPDATE
ON ord
FOR EACH ROW
BEGIN
UPDATE goods SET num = (num + old.num - new.num) WHERE gid = old.gid;
END$

#修改订单的商品数量
UPDATE ord SET num = 10 WHERE oid = 12;
#
SELECT * FROM ord;
#
SELECT * FROM goods;

#思考：tri_ord_update触发器中BEFORE和AFTER有什么区别
/*
思考：如果goods商品表中的某个商品数量剩余10，但
是下订单时，客户却买了该商品16个，会发生什么情况呢？能否预防呢？
能否在下单时的购买量>商品库存量时做出判断，此时就体现出BEFORE的
作用了
*/
#如下这条sql语句,就意味着下订单时，下单数量超过了商品的库存量(即爆仓)
INSERT INTO ord VALUES(13, 3, 196);

#创建触发器(解决下单数量超过了商品的库存量的问题)
/*
注意：在INSERT的时候，如果要改new的值，则必须是在INSERT之前(即BEFORE),因
为如果是AFTER的话，在INSERT的时候，new行已经插入到表中，成为事实，改new已
经晚了
*/
DELIMITER $
CREATE TRIGGER tri_ord_insert2
BEFORE #使用BEFORE
INSERT
ON ord
FOR EACH ROW
BEGIN
DECLARE goodNum int;#定义一个变量，用来接收某个商品的库存数量
#先查询某个商品的库存数量，然后把库存数量赋给变量
SELECT num INTO goodNum from goods where gid = new.gid;
IF new.num > goodNum #如果下单数量大于商品库存量
THEN SET new.num = goodNum; #则把下单数量改成商品库存量(即最多只能买商品的上限限制)
END IF;
UPDATE goods SET num = new.num WHERE gid = new.gid;
END$

#测试下单数量超过了商品的库存量(4号商品的库存量是800，这里下单的数量是805)
INSERT INTO ord VALUES(18, 4, 805);
#
SELECT * FROM ord;
#
SELECT * FROM goods;

/*
注意：
INSERT触发器 只能使用new
DELETE触发器 只能使用old
UPDATE触发器  既可以使用new又可以使用old
*/

#测试触发器中的for each row是干什么的？
CREATE TABLE temp01(
tid FLOAT,
tname VARCHAR(40)
)

#
INSERT INTO temp01 VALUES(1, '江西省于都县');
INSERT INTO temp01 VALUES(2, '江西省于都县');

#
SELECT * FROM temp01;

#
CREATE TABLE temp02(
tid FLOAT
)

#
SELECT * FROM temp02;

#创建触发器(测试触发器中的for each row是干什么的？)
DELIMITER $
CREATE TRIGGER tri_temp01_update
BEFORE
UPDATE
ON temp01
FOR EACH ROW
BEGIN
INSERT INTO temp02 VALUES(RAND());
END$

#for each row的意思就是行级触发器
#行级触发器: 每更新表中的一条记录, 都会导致触发器执行

#这里更新了该表中的多条记录，所以tri_temp01_update触发器也执行了多次
UPDATE temp01 SET tname = '江西省赣州市于都县';

#
SELECT * FROM temp02;

#创建触发器
DELIMITER $
CREATE TRIGGER tri_temp01_update2
AFTER #这里改成AFTER
UPDATE
ON temp01
FOR EACH ROW
BEGIN
#INSERT INTO temp02 VALUES(RAND());
INSERT INTO temp02 VALUES(666);
END$

#查询已有触发器
SHOW TRIGGERS;

#删除触发器
DROP TRIGGER tri_temp01_update2;

#清空temp02表
TRUNCATE temp02;