触发器的实践运用

6 篇文章 0 订阅
  • 我们还是不讲解知识点,直接上实践练习代码,亲自测试书写,运行正常。逻辑准确。

题目:

在这里插入图片描述

  • 数据表:
    在这里插入图片描述

解题

1.
delimiter $$
create trigger trigger_delete after delete on sell_order
for each row
BEGIN
DECLARE id int;
DECLARE num int;

select sellorderid into id from sell_order where sellorderid = old.sellorderid;
--  不能使用select 这样给num赋值
--  select  sellordernumber into num from sell_order where sellorderid = old.sellorderid;
set num = old.sellordernumber;

update product
set  product.productstocknumber = product.productstocknumber + num, product.productsellnumber = product.productsellnumber - num where product.productid = old.productid;
end $$
delimiter ;


DELETE from sell_order where sellorderid = 31;

drop TRIGGER trigger_delete;


2. 
delimiter $$
create trigger trigger_update after update on sell_order
for each row
BEGIN
DECLARE num int;
--  无论正负值,后面全用加法就可以了
set num = new.sellordernumber - old.sellordernumber;

update product
set  product.productstocknumber = product.productstocknumber - num, 
product.productsellnumber = product.productsellnumber + num 
where product.productid = old.productid;

end $$
delimiter ;

update sell_order
SET sell_order.sellordernumber = 100 where sellorderid = 8;

drop TRIGGER trigger_update;


3.
delimiter $$
create trigger trigger_deleted before delete on employee
for each row
BEGIN
DECLARE id int;
DECLARE num int;
-- 获得员工id值 
set id = old.employeeid;

# 这里必需给num 变量赋初始值,要不然,select赋值时,会出现 num = null的情况,得不到正确结果。
set num = 0;
select count(sellorderid) into num from sell_order where employeeid = id;
if num > 0 then 
	delete from sell_order where employeeid = id;
end if;

end $$
delimiter ;

delete from employee where employeeid = 3;

drop TRIGGER trigger_deleted;


-- 若生成触发器失败则执行:
select version(),
@@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值