Question

入库出库后库存自动更新的SQL触发器语句是什么?

tb1 (产品表)
cpid cpname cpcolor kucun
1 xxx xxx 8
2 xxx xxx 7
=================================
tb2 (入库表)
riqi ruku cpid
2011-1-10 5 1
2011-1-11 10 1
2011-1-12 15 2
=========================
tb3 (出库表)
riqi chuku cpid
2011-1-10 5 1
2011-1-11 2 1
2011-1-12 8 2
================================
入库出库后TB1的kucun字段中的数据自动更新,删除或修改后也要自动更新,请高手帮忙写个SQL触发器,问题解决追加100分,谢谢!
SQL版本2000
1楼,谢谢你的回答,但我觉得复杂了点呀
将入库表和出库表的cpid相同的数据用SUM语句统计出来,用ruku字段-chuku字段,最后将数据更新到产品表kucun
====================================================
下面是一个入库自动更新库存的语句,请大家参考后修改为入库出库都更新库存
参考语句:
Create TRIGGER [dbo].[tgname]
ON [dbo].[tb2]
AFTER INSERT,UPDATE,DELETE AS
BEGIN
update tb1 set kucun=(select sum(ruku) from tb2 where cpid=tb1.cpid)
END

Answer 1

CREATE trigger [tri_ruku]
on [tb2] for insert,delete,update
as
begin
update tb1 set kucun=kucun-deleted.ruku from tb1,deleted
where tb1.cpid=deleted.cpid;
update tb1 set kucun=kucun+inserted.ruku from tb1,inserted
where tb1.cpid=inserted.cpid
end;
----------------------------------------------------
CREATE trigger [tri_chuku]
on [tb3] for insert,delete,update
as
begin
update tb1 set kucun=kucun+deleted.chuku from tb1,deleted
where tb1.cpid=deleted.cpid;
update tb1 set kucun=kucun-inserted.chuku from tb1,inserted
where tb1.cpid=inserted.cpid
end;
------------------------------------------------------------------
-------------------------------------------------------
Create TRIGGER [dbo].[tgname]
ON [dbo].[tb2]
AFTER INSERT,UPDATE,DELETE AS
BEGIN
update tb1 set kucun=(select sum(ruku) from tb2 where cpid=tb1.cpid)
END
库存=入库-出库，这个语句修改一下也没问题，但t2中每条记录的改动都将导致t1的数据全部修改一遍，效率不高。
前两个触发器只影响t1的相关数据。

Answer 2

create trigger tr_insert_tb2 on tb2
for insert
as
begin
declare @cpid int
declare @cpname char(20)
declare @cpcolor char(2)
declare @kucun int
declare @ruku int
select @cpid=cpid,@cpname=cpname,@cpcolor=cpcolor,@ruku=ruku from inserted
select @kucun=kucun from tb1 where cpid=@cpid
if not exists(select cpid from tb1 where cpid=@cpid)
begin
insert into tb1 values(@cpid,@cpname,@cpcolor,@ruku)
update tb1 set kucun=(kucun+@ruku)
where cpid=@cpid
end
else
begin
select @kuncun=kucun from tb1 where cpid=@cpid
update tb1 set kucun=(kucun+@ruku)
where cpid=@cpid

end
end

create trigger tr_delete_tb2 on tb2
for delete
begin
declare @cpid int
declare @kucun int
declare @ruku int
select @cpid=cpid,@ruku=ruku from deleted
select @kucun=kucun from tb1 where cpid=@cpid
update tb1 set kucun=(kucun-@ruku) where cpid=@cpid
end

create trigger tr_update_tb2 on tb2
for update
begin
declare @cpid int
declare @kucun int
declare @oldruku int
declare @newruku int
select @cpid=cpid,@oldruku=ruku from deleted
select @cpid=cpid,@newruku=ruku from inserted
select @kucun=kucun from tb1 where cpid=@cpid
if(@kucun+@oldruku-@newruku)>=0
begin
update tb1 set kucun=(kucun+@oldruku-@newruku) where cpid=@cpid
end
end

create trigger tr_insert_tb3 on tb3
for insert
as
begin
declare @cpid int
declare @cpname char(20)
declare @cpcolor char(2)
declare @kucun int
declare @chuku int
select @cpid=cpid,@cpname=cpname,@cpcolor=cpcolor,@chuku=chuku from inserted
select @kucun=kucun from tb1 where cpid=@cpid
update tb1 set kucun=(kucun-@chuku)
where cpid=@cpid

end

create trigger tr_delete_tb3 on tb3
for delete
as
begin
declare @cpid int
declare @kucun int
declare @chuku int
select @cpid=cpid,@chuku=chuku from deleted
select @kucun=kucun from tb1 where cpid=@cpid
update tb1 set kucun=(kucun+@chuku) where cpid=@cpid
end

create trigger tr_update_tb3 on tb3
for update
as
begin
declare @cpid int
declare @kucun int
declare @oldchuku int
declare @newchuku int
select @cpid=cpid,@oldchuku=chuku from deleted
select @cpid=cpid,@newchuku=chuku from inserted
select @kucun=kucun from tb1 where cpid=@cpid
if(@kucun+@oldchuku-@newchuku)>=0
begin
update tb1 set kucun=(kucun+@oldchuku-@newchuku) where cpid=@cpid
end
end
无聊 就给你写了6个触发器 你可以看看 绝对没问题

Answer 3

触发器因为不同的数据库，脚本也不一样的。
我的建议，最好别用触发器，这个功能实际上通过编程很容易实现的