trigger.sql

来自「包含众多详细的delphi对数据库的访问代码」· SQL 代码 · 共 41 行

CREATE TRIGGER ut_GoodInDetail_UDI ON InStoreDetail FOR  DELETE , INSERT , UPDATE AS
BEGIN
  DECLARE @INO_I VARCHAR (12) , @IGOODSNO_I INT , @ISTORENO_I INT , @FQUANTITY_I Numeric (8,2)
  DECLARE @INO_D VARCHAR (12) , @IGOODSNO_D INT , @ISTORENO_D INT , @FQUANTITY_D Numeric (8,2)
          
  UPDATE Storage Set fQuantity = 0 WHERE fQuantity IS NULL

  DECLARE DELETED_CURSOR CURSOR FOR
       SELECT INO , IGoodsNo , fQuantity FROM DELETED;
  OPEN DELETED_CURSOR
  FETCH NEXT FROM DELETED_CURSOR INTO @INO_D , @IGOODSNO_D , @FQUANTITY_D
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @ISTORENO_D = ( SELECT iStore FROM InStoreMaster WHERE INO = @INO_D )
    IF EXISTS(SELECT * FROM Storage WHERE iStoreNo = @ISTORENO_D AND IGoodsNo = @IGOODSNO_D)
      UPDATE Storage Set fQuantity = fQuantity - @FQUANTITY_D WHERE iStoreNo = @ISTORENO_D AND IGoodsNo = @IGOODSNO_D
    ELSE
      INSERT INTO Storage (iStoreNo , iGoodsNo , fQuantity ) VALUES ( @ISTORENO_D , @IGOODSNO_D , - @FQUANTITY_D )
    FETCH NEXT FROM DELETED_CURSOR INTO @INO_D , @IGOODSNO_D , @FQUANTITY_D
  END
  CLOSE DELETED_CURSOR
  DEALLOCATE DELETED_CURSOR

  DECLARE INSERTED_CURSOR CURSOR FOR
       SELECT INO , IGoodsNo , fQuantity FROM INSERTED;
  OPEN INSERTED_CURSOR
  FETCH NEXT FROM INSERTED_CURSOR INTO @INO_I , @IGOODSNO_I , @FQUANTITY_I
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @ISTORENO_I = ( SELECT iStore FROM InStoreMaster WHERE INO = @INO_I )
    IF EXISTS(SELECT * FROM Storage WHERE iStoreNo = @ISTORENO_I AND IGoodsNo = @IGOODSNO_I)
      UPDATE Storage Set fQuantity = fQuantity + @FQUANTITY_I WHERE iStoreNo = @ISTORENO_I AND IGoodsNo = @IGOODSNO_I
    ELSE
      INSERT INTO Storage (iStoreNo , iGoodsNo , fQuantity ) VALUES ( @ISTORENO_I , @IGOODSNO_I , @FQUANTITY_I )
    FETCH NEXT FROM INSERTED_CURSOR INTO @INO_I , @IGOODSNO_I , @FQUANTITY_I
  END
  CLOSE INSERTED_CURSOR
  DEALLOCATE INSERTED_CURSOR

  DELETE FROM Storage WHERE fQuantity = 0
END