在“存入”表中制有如下触发器
CREATE TRIGGER km_ZFQ ON [dbo].[存入]
FOR INSERT, UPDATE, DELETE
AS
UPDATE 客户
SET 客户.账户余额 = (SELECT SUM(存入金额) FROM 存入 WHERE 存入.人员编号=客户.人员编号)
FROM 客户,存入
WHERE 客户.人员编号=存入.人员编号;
Alter TRIGGER GOODSINITTRIGGER1 ON GOODSINIT INSTEAD OF UPDATE AS
DECLARE @CurrYear INT, @CurrMonth INT
DECLARE @NEW_GOODSID T_KEYID, @NEW_YEARNUM T_KEYID_S, @NEW_MONTHNUM T_KEYID_S, @NEW_BQTY T_QUANTITY, @NEW_BPRICE T_PRICE, @NEW_INQTY
T_QUANTITY, @NEW_OUTQTY T_QUANTITY, @NEW_EQTY T_QUANTITY, @NEW_EPRICE T_PRICE, @NEW_BAMOUNT T_AMOUNT, @NEW_EAMOUNT T_AMOUNT
DECLARE @OLD_GOODSID T_KEYID, @OLD_YEARNUM T_KEYID_S, @OLD_MONTHNUM T_KEYID_S, @OLD_BQTY T_QUANTITY, @OLD_BPRICE T_PRICE, @OLD_INQTY
T_QUANTITY, @OLD_OUTQTY T_QUANTITY, @OLD_EQTY T_QUANTITY, @OLD_EPRICE T_PRICE, @OLD_BAMOUNT T_AMOUNT, @OLD_EAMOUNT T_AMOUNT
DECLARE NEWED CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT GOODSID, YEARNUM, MONTHNUM, BQTY, BPRICE, INQTY, OUTQTY,
EQTY, EPRICE, BAMOUNT, EAMOUNT FROM INSERTED Order By GOODSID,YearNum,MonthNum
DECLARE OLDED CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR SELECT GOODSID, YEARNUM, MONTHNUm, BQTY, BPRICE, INQTY, OUTQTY,
EQTY, EPRICE, BAMOUNT, EAMOUNT FROM DELETED Order By GOODSID,YearNum,MonthNum
OPEN NEWED
OPEN OLDED
FETCH NEXT FROM NEWED INTO @NEW_GOODSID, @NEW_YEARNUM, @NEW_MONTHNUM, @NEW_BQTY, @NEW_BPRICE, @NEW_INQTY, @NEW_OUTQTY, @NEW_EQTY,
@NEW_EPRICE, @NEW_BAMOUNT, @NEW_EAMOUNT
IF (@@FETCH_STATUS = 0)
FETCH NEXT FROM OLDED INTO @OLD_GOODSID, @OLD_YEARNUM, @OLD_MONTHNUM, @OLD_BQTY, @OLD_BPRICE, @OLD_INQTY, @OLD_OUTQTY ,@OLD_EQTY, @OLD_EPRICE, @OLD_BAMOUNT, @OLD_EAMOUNT
WHILE (@@FETCH_STATUS = 0)
BEGIN
--BEGIN TRANSACTION
SET @NEW_EQTY = @NEW_BQTY + @NEW_INQTY - @NEW_OUTQTY
IF (@NEW_BQTY <> @OLD_BQTY OR @NEW_BPRICE <> @OLD_BPRICE)
SET @NEW_BAMOUNT = @NEW_BQTY * @NEW_BPRICE
IF (- 0.0000001 < @NEW_EQTY AND @NEW_EQTY < 0.0000001)
SET @NEW_EQTY = 0
IF (@NEW_EAMOUNT = @OLD_EAMOUNT AND @NEW_BAMOUNT <> @OLD_BAMOUNT)
SET @NEW_EAMOUNT = @OLD_EAMOUNT + @NEW_BAMOUNT - @OLD_BAMOUNT
IF (@NEW_BQTY <> @OLD_BQTY OR @NEW_BPRICE <> @OLD_BPRICE)
BEGIN
SELECT @CURRYEAR = POSYEAR, @CURRMONTH = POSMON FROM ACCINFO
UPDATE DETAILBILLFLOW SET CACULATEFLAG = 'F' WHERE BILLDATE >= CAST (convert (varchar,@CURRMONTH) + '/01/' + convert (varchar,@CURRYEAR) AS DATETIME) AND GOODSID = @OLD_GOODSID
END
UPDATE GOODSINIT SET GOODSID = @NEW_GOODSID, YEARNUM = @NEW_YEARNUM, MONTHNUM = @NEW_MONTHNUM, BQTY = @NEW_BQTY, BPRICE = @NEW_BPRICE, INQTY = @NEW_INQTY, OUTQTY = @NEW_OUTQTY, EQTY = @NEW_EQTY, EPRICE = @NEW_EPRICE, BAMOUNT = @NEW_BAMOUNT,
EAMOUNT = @NEW_EAMOUNT
WHERE GOODSINIT.GOODSID = @OLD_GOODSID AND GOODSINIT.YEARNUM = @OLD_YEARNUM AND GOODSINIT.MONTHNUM = @OLD_MONTHNUM
FETCH NEXT FROM NEWED INTO @NEW_GOODSID, @NEW_YEARNUM, @NEW_MONTHNUM, @NEW_BQTY, @NEW_BPRICE, @NEW_INQTY, @NEW_OUTQTY, @NEW_EQTY, @NEW_EPRICE, @NEW_BAMOUNT, @NEW_EAMOUNT
IF (@@FETCH_STATUS = 0)
FETCH NEXT FROM OLDED INTO @OLD_GOODSID, @OLD_YEARNUM, @OLD_MONTHNUM, @OLD_BQTY, @OLD_BPRICE, @OLD_INQTY, @OLD_OUTQTY, @OLD_EQTY, @OLD_EPRICE, @OLD_BAMOUNT, @OLD_EAMOUNT
END
CLOSE NEWED
DEALLOCATE NEWED
CLOSE OLDED
DEALLOCATE OLDED