计算库存触发器.txt

是分布式粮库程序,是采用Delphi实现的

CREATE TRIGGER [U_CalculateStorage] ON [dbo].[ENT_OUTSTACK] 
FOR UPDATE
AS
BEGIN
  DECLARE
  @InStack_ID VARCHAR(12),
  @OutQuantity DECIMAL(10,3),
  @OutOld int,
  @OutNew int,
  @OutMisc int
  Select @InStack_ID=INSTACK_ID from DELETED
  Select @OutQuantity=IsNull(Sum(Out_Quantity),0),
         @OutOld=IsNull(Sum(Old),0),
         @OutNew=IsNull(Sum(New),0),
         @OutMisc=IsNull(Sum(Misc),0)
    from ENT_OUTSTACK 
    Where INSTACK_ID=@InStack_ID
  Update ENT_INSTACK Set
    STORAGE=IN_QUANTITY-@OutQuantity,
    R_New=New-@OutNew,
    R_Old=Old-@OutOld,
    R_Misc=Misc-@OutMisc
    Where INSTACK_ID=@InStack_ID

  Select @InStack_ID=INSTACK_ID from INSERTED
  Select @OutQuantity=Sum(Out_Quantity),
         @OutOld=IsNull(Sum(Old),0),
         @OutNew=IsNull(Sum(New),0),
         @OutMisc=IsNull(Sum(Misc),0)
    from ENT_OUTSTACK 
    Where INSTACK_ID=@InStack_ID
  Update ENT_INSTACK Set
    STORAGE=IN_QUANTITY-@OutQuantity,
    R_New=New-@OutNew,
    R_Old=Old-@OutOld,
    R_Misc=Misc-@OutMisc
    Where INSTACK_ID=@InStack_ID
end;


CREATE TRIGGER [D_CalculateStorage] ON [dbo].[ENT_OUTSTACK] 
FOR DELETE 
AS
BEGIN
  DECLARE
  @InStack_ID VARCHAR(12),
  @OutQuantity DECIMAL(10,3),
  @OutOld int,
  @OutNew int,
  @OutMisc int  
  Select @InStack_ID=INSTACK_ID from DELETED
  Select @OutQuantity=IsNull(Sum(Out_Quantity),0),
         @OutOld=IsNull(Sum(Old),0),
         @OutNew=IsNull(Sum(New),0),
         @OutMisc=IsNull(Sum(Misc),0)
    from ENT_OUTSTACK 
    Where INSTACK_ID=@InStack_ID
  Update ENT_INSTACK Set
    STORAGE=IN_QUANTITY-@OutQuantity,
    R_New=New-@OutNew,
    R_Old=Old-@OutOld,
    R_Misc=Misc-@OutMisc
    Where INSTACK_ID=@InStack_ID
end;


CREATE TRIGGER [I_CalculateStorage] ON [dbo].[ENT_OUTSTACK] 
FOR INSERT 
AS
BEGIN
  DECLARE
  @InStack_ID VARCHAR(12),
  @OutQuantity DECIMAL(10,3),
  @OutOld int,
  @OutNew int,
  @OutMisc int
  Select @InStack_ID=INSTACK_ID from INSERTED
  Select @OutQuantity=IsNull(Sum(Out_Quantity),0),
         @OutOld=IsNull(Sum(Old),0),
         @OutNew=IsNull(Sum(New),0),
         @OutMisc=IsNull(Sum(Misc),0)
    from ENT_OUTSTACK 
    Where INSTACK_ID=@InStack_ID
  Update ENT_INSTACK Set
    STORAGE=IN_QUANTITY-@OutQuantity,
    R_New=New-@OutNew,
    R_Old=Old-@OutOld,
    R_Misc=Misc-@OutMisc
    Where INSTACK_ID=@InStack_ID
end;


CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

CREATE TRIGGER [CalculateStorage] ON [dbo].[ENT_INSTACK]
FOR UPDATE
AS
BEGIN
  @OldInStack_ID VARCHAR(12),
  @NewInStack_ID VARCHAR(12),
  @OldQuantity DECIMAL(10,3),
  @NewQuantity DECIMAL(10,3),
  Select @OldInStack_ID=INSTACK_ID, @OldQuantity=IN_QUANTITY from DELETED
  Select @NewInStack_ID=INSTACK_ID, @NewQuantity=IN_QUANTITY from INSERTED
  IF @OldInStack_ID=@NewInStack_ID 
  BEGIN
    update ENT_INSTACK 
    SET STORAGE=STORAGE+@NewInStack_ID-@OldInStack_ID
  END
END;