Question

急需！SQL创建一个触发器，要求当插入、更新、删除销售表的销售记录时，根据销售数量能更新产品表

创建一个触发器，要求当插入、更新、删除销售表的销售记录时，根据销售数量，，能更新产品表中相应的库存数量

create trigger t_Stocks on Product
for insert,delete,update
as
begin
update Product set Stocks = Stocks - Quantity where ProNo=(select ProNo fromINSERTED)
update Product set Stocks = Stocks + Quantity where ProNo=(select ProNo fromDELETED)
end
网上搜了个答案，没有update 记录时如何改变库存量，求完整准确的答案，急需！

Answer 1

t_spxsrb=销售日报 t_spkcmx=商品库存明细 kcsl=库存数量 xssl=销售数量 spbm=商品编码
create or replace trigger trigger_DML
before insert or update or delete on t_spxsrb
for each row
begin
if updating then
update t_spkcmx set kcsl=kcsl-(:new.xssl-:old.xssl) where spbm=:old.spbm;
dbms_output.put_line('修改');
elsif deleting then
update t_spkcmx set kcsl=kcsl+:old.xssl where spbm=:old.spbm;
dbms_output.put_line('删除');
elsif inserting then
update t_spkcmx set kcsl=kcsl-:new.xssl where spbm=:new.spbm;
dbms_output.put_line('插入');
end if;
end trigger_DML;

Answer 2

t_spxsrb=销售日报 t_spkcmx=商品库存明细 kcsl=库存数量 xssl=销售数量 spbm=商品编码
create or replace trigger trigger_DML
before insert or update or delete on t_spxsrb
for each row
begin
if updating then
update t_spkcmx set kcsl=kcsl-(:new.xssl-:old.xssl) where spbm=:old.spbm;
dbms_output.put_line('修改');
elsif deleting then
update t_spkcmx set kcsl=kcsl+:old.xssl where spbm=:old.spbm;
dbms_output.put_line('删除');
elsif inserting then
update t_spkcmx set kcsl=kcsl-:new.xssl where spbm=:new.spbm;
dbms_output.put_line('插入');
end if;
end trigger_DML;

Answer 3

update Product set Stocks = Stocks - inerted.Quantity +delete.Quantity
where ProNo=(select ProNo from Inserted) and Inserted.ProNo =Deleted.ProNo
更新就是把新生成的记录到inserted表，复制旧的记录到deleted表，然後删除旧记录插入新纪录