You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
70 lines
1.8 KiB
70 lines
1.8 KiB
##测试
|
|
-- 第一个触发器
|
|
drop TRIGGER if EXISTS updateOd;
|
|
create Trigger updateOd
|
|
after update
|
|
on orderdetails for each ROW
|
|
begin
|
|
declare done int default 0;
|
|
DECLARE dST VARCHAR(10);
|
|
DECLARE flag int DEFAULT 1;
|
|
-- 创建游标，并存储数据
|
|
declare cur_test cursor for select deliveryStatus from orderdetails where orderNo = old.orderNo;
|
|
-- 游标中的内容执行完后将done设置为1
|
|
declare continue handler for not found set done = 1;
|
|
-- 打开游标
|
|
open cur_test;
|
|
-- 执行循环
|
|
posLoop:loop
|
|
-- 判断是否结束循环
|
|
IF done = 1 THEN
|
|
LEAVE posLoop;
|
|
END IF;
|
|
-- 取游标中的值
|
|
FETCH cur_test into dST;
|
|
IF dST = '已配送' THEN
|
|
set @flag = 1;
|
|
ELSE
|
|
set @flag = 0;
|
|
END IF;
|
|
end loop;
|
|
-- 释放游标
|
|
close cur_test;
|
|
IF @flag = 1 then
|
|
UPDATE orders SET orderStatus = '已处理' WHERE orderNo=old.orderNo;
|
|
end if;
|
|
end;
|
|
|
|
set names utf8 ;
|
|
-- update orderdetails set deliveryStatus = '已配送' WHERE orderNo = '00000012020-07-02 16:31:15';
|
|
|
|
|
|
|
|
-- 第三个触发器
|
|
drop TRIGGER if EXISTS updateUser;
|
|
create Trigger updateUser
|
|
after update
|
|
on orders for each ROW
|
|
begin
|
|
declare sm int default 0;
|
|
declare um varchar(10);
|
|
select sumMoney into sm from orders WHERE orderNo=old.orderNo;
|
|
select userNo into um from orders WHERE orderNo=old.orderNo;
|
|
update users set level = floor(sm/100) where users.userNo = um;
|
|
end;
|
|
|
|
|
|
-- 第一个触发器
|
|
drop TRIGGER if EXISTS updateDBL;
|
|
create Trigger updateDBL
|
|
after update
|
|
on distributionlist for each ROW
|
|
begin
|
|
IF new.deliveryStatus = '已完成配送' THEN
|
|
update orderdetails SET deliveryStatus = '已配送' where orderNo = old.orderNo;
|
|
END IF;
|
|
end;
|
|
|
|
-- 这里测试的时候需要修改orderNo的实时订单号
|
|
update distributionlist set deliveryStatus = '已完成配送' WHERE orderNo = '00000012020-07-02 16:53:29';
|