触发器完成级联删除

定义BEFOR触发器和AFTER触发器。能够理解不同类型触发器的作用和执行原理。验证触发器的有效性。
环境：Navicat for MySQL
一．AFTER触发器
(1)在Lineitem表上定义一个update触发器，当修改订单明细（即修改订单明细 Lineitem价格extendedprice，折扣discount，税率tax自动修改订单Orders的TotalPrice，以保持数据一致性。
(2)在Lineitem表上定义一个insert触发器，当增加一项订单明细时，自动修改订单Orders的TotalPrice，以保持数据的一致性。
(3)在Lineitem表上定义一个delete触发器，当删除一项订单明细时，自动修改订单Orders的TotalPrice，以保持数据的一致性。
(4)验证触发器TRI_Lineitem_Price_update。
二．BEFOR触发器
(1)在Lineitem表上定义一个before update触发器，当修改订单明细中的数量（quantity）。先检查供应表PartSupp中的可用数量availqty是否足够。
(2)在Lineitem表上定义一个before insert触发器，当修改订单明细，先检查供应表PartSupp中的可用数量availqty是否足够。
(3)在Lineitem表上定义一个before delete触发器，当删除订单明细，该订单明细项订购的数量要归还对应的零件供应记录。
(4)验证触发器TRI_Lineitem_Quantity_update。
具体的程序功能：

#1after触发器#
#(1)total price=totalprice+extendedprice*(1-discount)*(1+tax)#
create  trigger TRI_Lineitem_Price_update
after update on  Lineitem 
for each row
begin
update Orders 
set 
totalprice=totalprice+(new.extendedprice*(1-new.discount)*(1+new.tax)
-old.extendedprice*(1-old.discount)*(1+old.tax))
where orderskey=new.orderskey;
end;
//(2)
create trigger TRI_Lineitem_Price_insert
after insert on Lineitem
for each row
begin 
update Orders set totalprice
=totalprice+NEW.extendedprice*(1-NEW.discount)*(1+NEW.tax)
where orderskey=NEW.orderskey;
end;
//(3)
create trigger TRI_Lineitem_Price_delete
after delete on Lineitem
for each row
begin
update Orders set totalprice
=totalprice-OLD.extendedprice*(1-OLD.discount)*(1+OLD.tax)
where orderskey=orderskey;end;
#验证触发器TRI_Lineitem_Price_update#
#查看1854号订单的含税折扣总价totalprice#
select totalprice from orders where orderskey =1854;
#激活触发器，修改1854号订单第一个明细项的税率，该税率增加0.5%#
update Lineitem set tax =tax+0.05where orderskey=1854 and linenumber=1;
#再次查看2号订单的含税折扣总价totalprice是否有变化#
select totalprice from orders where orderskey =1854;

#2.before触发器#

//（1）
create trigger TRI_Lineitem_Quantity_update
before update on Lineitem
for each row
begin
select availqty into @L_availqty from partsupp
where partkey=new.partkey and suppkey=new.suppkey;
if(@L_availqty-(new.partkey-old.quantity)>=0) then
begin
update partsupp set availqty=availqty-(new.quantity)
where partkey=new.partkey and suppkey=new.suppkey ;
end;
end if;
end;
// （2）
create trigger TRI_Lineitem_Quantity_insert
before insert on Lineitem
for each rowbegin
select availqty into @L_availqty from partsupp
where partkey=new.partkey and suppkey=new.suppkey;
if(@L_availqty-(new.partkey)>=0) then
begin
update partsupp set availqty=availqty-(new.quantity)
where partkey=new.partkey and suppkey=new.suppkey ;
end;
end if;
end;
//（3）
create trigger TRI_Lineitem_Quantity_delete
before
delete on Lineitem
for each row
begin
update partsupp set availqty=availqty-(old.quantity)
where partkey=old.partkey and suppkey=old.suppkey;
end;

4.验证触发器TRI_Lineitem_Quantity_update#

#查看1854号订单第1个明细项的零件和供应商编号，订购数量，可用数量#
select L.partkey,L.suppkey,L.quantity,PS.availqty
from Lineitem L,PartSupp PS
where L.partkey=PS.partkey 
and L.suppkey=PS.suppkeyand L.orderskey=1854 
and L.Linenumber=1;
#激活触发器：修改1854号订单第1个明细项的订购数量#
update Lineitem set quantity=quantity+5
where orderskey=1854 and linenumber=1;
#再次查看1854订单第1个明细项的相关信息，以验证触发器是否起作用#
select L.partkey,L.suppkey,L.quantity,PS.availqty
from Lineitem L,PartSupp PS
where L.partkey=PS.partkey 
and L.suppkey=PS.suppkeyand L.orderskey=1854 
and L.Linenumber=1;
#删除触发器#
drop trigger TRI_Lineitem_Price_update;