create TRIGGER gtProductInComU on tProductInCom
for update
AS
begin
declare @OldSubmitFlag bit,@NewSubmitFlag bit
declare @ID int,@DepotIn int
declare @NO nvarchar(20),@Maker nvarchar(20)
if Update(fSubmitFlag)
begin
select @OldSubmitFlag=fSubmitFlag from Deleted
select @NewSubmitFlag=fSubmitFlag from Inserted
if (@OldSubmitFlag=0) and (@NewSubmitFlag=1)
begin
Select @ID=fID,@DepotIn=isnull(fDepotIn,0),@NO=fNo,@Maker=fSubmitUser from Inserted
--插入仓库没的的产品
Insert into tDepotTotal(fDepotID,fProductID)
Select @DepotIn,a.fResID
from tProductSub a
where a.fID=@ID and a.ResID Not in(Select fProductID from tDepotTotal where fDepotID=@DepotIn)
--更新仓库库存数量
Update a Set a.fCurQty=a.fCurQty+b.fQty
from tDepotTotal a,tProductInSub b
where a.fProductID=b.fResID and b.fID=@ID and fDepotID=@DepotIn
--记仓库流水帐
Insert into tDepIO(fDepotID,fProductID,fDate,fName,fNO,fInQty,fBalQty,fPrePrice,fIOPrice,fMaker)
Select @DepotIn,a.fResId,Getdate(),'入库',@NO,a.fQty,b.fCurQty,c.fPrePrice,c.fPrePrice,@Maker
from tProductInSub a,tDepotTotal b,tBaseProduce c
where a.fID=@ID and b.fDeportID=@DepotIn and a.fResID=b.fProductID and a.fResID=c.fID
end
if (@OldSubmitFlag=1) and (@NewSubmitFlag=0)
begin
Select @ID=fID,@DepotIn=isnull(fDepotIn,0),@NO=fNo,@Maker=fSubmitUser from Inserted
--插入仓库没的的产品
Insert into tDepotTotal(fDepotID,fProductID)
Select @DepotIn,a.fResID
from tProductSub a
where a.fID=@ID and a.ResID Not in(Select fProductID from tDepotTotal where fDepotID=@DepotIn)
--更新仓库库存数量
Update a Set a.fCurQty=a.fCurQty-b.fQty
from tDepotTotal a,tProductInSub b
where a.fProductID=b.fResID and b.fID=@ID and fDepotID=@DepotIn
--记仓库流水帐
Insert into tDepIO(fDepotID,fProductID,fDate,fName,fNO,fInQty,fBalQty,fPrePrice,fIOPrice,fMaker)
Select @DepotIn,a.fResId,Getdate(),'入库(弃审)',@NO,a.fQty,b.fCurQty,c.fPrePrice,c.fPrePrice,@Maker
from tProductInSub a,tDepotTotal b,tBaseProduce c
where a.fID=@ID and b.fDeportID=@DepotIn and a.fResID=b.fProductID and a.fResID=c.fID
end
修改方法:
1.删除AAA.tProductInCom这张表,这样处理最保险,避免以后程序中数据操作的混淆,同时注意以后不论创建、修改数据对象的时候,在对象名称前面加入DBO.前缀,以免出现重名的现象。
2.修改创建触发器的语句的提头
create TRIGGER gtProductInComU on DBO.tProductInCom
for update
AS
……
----------例如----------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER InsertNewAffiche ON dbo.TAffiche
FOR INSERT
AS
declare @nNewId int
declare @nkey int
select @nNewId =id,@nkey= nkey from inserted
if @nkey=0
update TAffiche set nkey=@nNewId
where id=@nNewId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO