库存表kc(spno,spgg,pcs)
销售从表b(spno,spgg,pcs)现在删除销售单,此单的从表b中有2条以上记录,在从表b中用触发器分别从库存表kc中相应减数CREATE TRIGGER [xsd_delete] ON dbo.b
FOR delete
ASdeclare @spno varchar(20)
declare @spgg varchar(40)
declare @pcs floatset @spno = (select spno from deleted)
set @spgg = (select spgg from deleted)
set @pcs = (select pcs from deleted)update kc set pcs = pcs - @pcs
where spno=@spno and spgg = @spgg在一条记录时可以,但多条记录就出错
销售从表b(spno,spgg,pcs)现在删除销售单,此单的从表b中有2条以上记录,在从表b中用触发器分别从库存表kc中相应减数CREATE TRIGGER [xsd_delete] ON dbo.b
FOR delete
ASdeclare @spno varchar(20)
declare @spgg varchar(40)
declare @pcs floatset @spno = (select spno from deleted)
set @spgg = (select spgg from deleted)
set @pcs = (select pcs from deleted)update kc set pcs = pcs - @pcs
where spno=@spno and spgg = @spgg在一条记录时可以,但多条记录就出错
FOR delete
ASdeclare @spno varchar(20)
declare @spgg varchar(40)
declare @pcs floatDeclare MyCursor Cursor for
Select spno,spgg,pcs from Deleted
Open MyCursor
Fetch Next from MyCursor into @spno,@spgg,@pcs
While @@Fetch_Status=0
begin
update kc set pcs = pcs - @pcs
where spno=@spno and spgg = @spgg
Fetch Next from MyCursor into @spno,@spgg,@pcs
end
Close MyCursor
DeAllocate MyCursor