MSSQL2000触发器怎么会这样？能帮看看吗？

readersm68(地主) 能否放上一段你的存储过程，非常的感谢。

还有一点需要补充一下：当我对A2表中lsh字段值逐行修改，其A3表的结果是预期的结果，但如果只改A1表的lsh值，则会出现上述的问题，不知道问题根节在哪，望高手指点一下，谢谢谢谢。

/*19 设置商品出库*/
ALTER PROCEDURE  SMSP_SetOKPOutStore
  @OrderOutOdd01 varchar(20), /*预出库单编号*/
  @OutStoreOdd01 varchar(20), /*出库单编号*/
  @ReturnInfo varchar(100) OUTPUT /*返回信息*/
AS
DECLARE
  @RecordLogNum integer,  /*验货没有质检的记录数量*/
  @RecordOutStoreOdd integer, /*出库订单的记录数*/
  @Goods01 varchar(30),      /*商品编号*/
  @Goods22 numeric(15,6),    /*移动均价*/
  @OutNum  Numeric(11,2),      /*出库数量*/
  @StorageNum Numeric(11,2),   /*库存数量*/
  @SaleMoney numeric(11,2), /*出库金额*/
  @CostMoney numeric(11,2), /*出库成本，按移动均价*/
  @OutLog01 integer,  /*出库清单编号*/
  @SumResNum Numeric(11,2),/*订单剩余数量*/
  @Type varchar(12), /*预出库编号头*/
  @SalePrice Numeric(15,6),      /*出库单价*/
    @Rebate numeric(4,2),      /*折扣率*/
    @Goods23 numeric(11,2),      /*调拨价*/
    @Goods25 numeric(11,2),     /*批发价*/
    @OutType varchar(20),       /*出库类型*/
  @Note varchar(100),     /*备注*/
  @ISSucceed varchar(10)  /*返回十分成功*/  /*查找出库单中是否已有订单出库*/
  SELECT @RecordOutStoreOdd= Count(*)
  FROM OutStoreOdd
  WHERE OrderOutOdd01=@OrderOutOdd01

  IF ISNULL(@RecordOutStoreOdd,0) > 0
  BEGIN
    SET @ReturnInfo  ='该预出库单已经出库。'
    RETURN
  END  /*查找已验货的记录，如果没有，退出*/
  SELECT @RecordLogNum=Count(*)
  FROM OrderOutLog
  WHERE ( OrderOutOdd01=@OrderOutOdd01 )
  AND OrderOutLog09 is not Null  IF ISNULL(@RecordLogNum,0) =0
  BEGIN
    SET @ReturnInfo  ='还没有验货，请验货后再出库。'
    RETURN
  END

  /*查找验货、未质检的清单记录*/
  SELECT @RecordLogNum=Count(*)
  FROM OrderOutLog
  WHERE ( OrderOutOdd01=@OrderOutOdd01 )
  AND (OrderOutLog09 is not Null)
  AND (OrderOutLog07 is  Null)   IF ISNULL(@RecordLogNum,0) >0
  BEGIN
    SET @ReturnInfo  ='有'+LTRIM(STR(@RecordLogNum))+'条出库清单记录，还没有质检，请质检完再出库。'
    RETURN
  END
  ELSE
  BEGIN
    /*开始事务，创建出库单，插入清单，生成明细，更新库存结存数，更新预出库单状态*/
    BEGIN TRANSACTION
       /*创建出库单1条*/
       EXEC SMSP_InsertOutStoreOdd @OrderOutOdd01,@OutStoreOdd01      /*插入出库清单记录n条*/
      DECLARE OrderOutLog CURSOR  LOCAL FOR
      SELECT Goods01,OrderOutLog05,OrderOutLog03
      FROM OrderOutLog
      WHERE ( OrderOutOdd01=@OrderOutOdd01 )
      AND (OrderOutLog09 is not Null)
      AND (OrderOutLog07 is not Null)      OPEN OrderOutLog
      FETCH NEXT FROM OrderOutLog INTO @Goods01,@OutNum,@SalePrice
      WHILE @@FETCH_STATUS = 0
      BEGIN         /*查找移动均价*/
        SELECT @Goods22=Goods22,@Goods23=Goods23,@Goods25=Goods25
        FROM Goods
        WHERE Goods01=@Goods01
        IF @Goods23 IS NULL
        BEGIN
          SET @Goods23=0
        END
        IF @Goods25 IS NULL
        BEGIN
          SET @Goods25=0
        END      SELECT @OutType=OrderOutOdd29 FROM OrderOutOdd
      WHERE OrderOutOdd01=@OrderOutOdd01        IF @OutType='调拨'
      BEGIN
          IF @Goods23<>0
          BEGIN
          SET @Rebate=1-@SalePrice/@Goods23
          SET @Note='调拨价：'+CONVERT(varchar(10), @Goods23)+'折扣率：'+CONVERT(varchar(5),@Rebate)
        END
        ELSE
        BEGIN
          SET @Note='调拨价：0'
        END
      END
      ELSE IF @OutType='批发'
      BEGIN
        IF @Goods25<>0
        BEGIN
          SET @Rebate=1-@SalePrice/@Goods25
          SET @Note='批发价：'+CONVERT(varchar(10), @Goods23)+'折扣率：'+CONVERT(varchar(5),@Rebate)
        END
        ELSE
        BEGIN
          SET @Note='批发价：0'
        END
      END
      ELSE
      BEGIN
        SET @Note='---'
      END             /*插入1条清单记录到出库清单表中*/
        INSERT INTO OutLog
        EXEC SMSP_SelectOrderOutLog @OrderOutOdd01,@OutStoreOdd01,@Goods01,@Goods22,@Note        /*得到刚插入的OutLog01出库清单编号的值*/
        SELECT @OutLog01=OutLog01
        FROM OutLog
        WHERE OutStoreOdd01=@OutStoreOdd01
        AND Goods01=@Goods01         IF @OutLog01 IS NULL
        BEGIN
          SET @ReturnInfo  ='没有找到'+@OutStoreOdd01+'<'+@Goods01+'>的出库清单记录。'
          ROLLBACK TRANSACTION
          CLOSE OrderOutLog
          DEALLOCATE OrderOutLog
          RETURN
        END        /*判断库存量是否小于出库数量，是退出，否调用插入出库清单明细存储过程*/
          SELECT @StorageNum=SUM(Storage12)
          FROM Storage
          WHERE Goods01=@Goods01
          AND Storage12>0

          IF ISNULL(@StorageNum,0) < @OutNum
          BEGIN
            SET @ReturnInfo  ='商品:'+@Goods01+'的库存量小于出库量,无法出库。'
            ROLLBACK TRANSACTION
            CLOSE OrderOutLog
            DEALLOCATE OrderOutLog
            RETURN
          END
          ELSE /*IF ISNULL(@StorageNum,0) >=@OutNum*/
          BEGIN
            /*更新库存结存数，插入出库清单明细*/
            EXEC SMSP_UpdateStorage @OutLog01,@Goods01,@OutNum,@SalePrice,@ISSucceed OUTPUT
            IF @ISSucceed='Failed'
            BEGIN
              SET @ReturnInfo  ='向出库清单明细表中插入记录失败。'
              ROLLBACK TRANSACTION
              CLOSE OrderOutLog
              DEALLOCATE OrderOutLog
              RETURN
            END
          END        FETCH NEXT FROM OrderOutLog INTO @Goods01,@OutNum,@SalePrice
      END
      CLOSE OrderOutLog
      DEALLOCATE OrderOutLog      /*更新出库单金额（按出库单价合计）、成本(按移动均价合计)，更新预出库单状态为7 完成*/
      SELECT @SaleMoney= SUM(OutLog03*OutLog06),@CostMoney=SUM(OutLog03*OutLog09)
      FROM OutLog
      WHERE OutStoreOdd01=@OutStoreOdd01      UPDATE OutStoreOdd SET OutStoreOdd05=@SaleMoney,OutStoreOdd08=@CostMoney
      WHERE OutStoreOdd01=@OutStoreOdd01      UPDATE OrderOutOdd SET  OrderOutOdd18='7 完成'
      WHERE OrderOutOdd01=@OrderOutOdd01    COMMIT
    /*查询预出库数量的剩余总量,有需要提示*/
    SELECT @SumResNum=SUM(OrderOutLog06)
    FROM OrderOutLog
    WHERE OrderOutOdd01=@OrderOutOdd01    /*查找预出库单是否是销订*/
    SELECT @Type=SUBSTRING(OrderOutOdd01,1,2)
    FROM OrderOutOdd
    WHERE OrderOutOdd01=@OrderOutOdd01
    IF (ISNULL(@SumResNum,0)>0) AND (ISNULL(@Type,' ')= '销订')
    BEGIN
      SET @ReturnInfo='出库成功！已成功生成出库单:'+@OutStoreOdd01+'。剩余数量，请选择菜单中的<拆分订单>转移到新订单中。'
    END
    ELSE
    BEGIN
      SET @ReturnInfo='出库成功！已成功生成出库单:'+@OutStoreOdd01
    END
  END/*--------------------------*/
GO

>>如果a2表中有多行数据被A1触发器更新了，A2表触发器只会对该表的最后一个记录做相应的动作，不知道是SQL原本如此还是须要设定什么参数>>非常的感谢，不知道是否必须用存储过程才能解决这样的问题。这样的触发器不知道该如何正确写。參考下SQL SERVER 的幫助, 你的這種, 應該用個循環來讀取更新就可

谢谢 aiirii(ari-爱的眼睛)，我也考虑过这样做，总觉得这样不好，始终搞不清，为什么A1表执行了update a2 set ghs=值 where lsh=值
能更新A2表的相应值，但为什么A2表的ghs触发器只触发了最后一条记录呢？真的搞不懂。

建议你用游标试试,
相关详细SQL帮助

>>能更新A2表的相应值，但为什么A2表的ghs触发器只触发了最后一条记录呢？真的搞不懂。新手會犯的錯誤了, 你以為每次對應update一條記錄, 其實, 是一個批處理的方式,
A1 修改 A2 多條記錄, 可能只觸發一次 A3 的觸發器而已

>>>是一個批處理的方式
我在A1触发器中用到SQL标准update语句来更新A2表的。不知道是否是批处理方式>>>>A1 修改 A2 多條記錄, 可能只觸發一次 A3 的觸發器而已
从目前得到结果看的情况看应该是这样的，但为什么A1执行一次update更新A2多条记录，只会触发一次呢？如何才可以解决这一问题呢？游标我也试过，也只触发一次(是当A2最后一条记录被A1触发器更新时。)

好的。
这是在A1表创建的触发器：
                    TIGGSQLSTR:='CREATE TRIGGER '+LAddTrigName+' ON dbo.lsh_table '+#13+
                                 'FOR UPDATE '+#13+
                                 'AS '+#13+
                                 'Set Nocount On '+#13+
                                 'BEGIN TRANSACTION '+#13+
                                 'IF UPDATE (LsH_ghs) '+#13+
                                 'begin '+#13+
                                     'DECLARE @lsh varchar(17) '+#13+
                                     'DECLARE @ghs varchar(40) '+#13+
                                     'select @lsh=lsh_code,@ghs=lsh_ghs from inserted '+#13+
                                     'update changku_diaoru_table set diaoru_ghs=@ghs where diaoru_lsh=@lsh '+#13+
                                     'update changku_diaochu_table set diaochu_ghs=@ghs where diaochu_lsh=@lsh '+#13+
                                 'end '+#13+                                 'IF UPDATE(LsH_changku) '+#13+
                                 'begin '+#13+
                                     'DECLARE @changku_lsh varchar(17) '+#13+
                                     'DECLARE @changkuname varchar(20) '+#13+
                                     'select @changku_lsh=lsh_code,@changkuname=LsH_changku from inserted '+#13+
                                     'update changku_diaoru_table set diaoru_formChangku=@changkuname where diaoru_lsh=@changku_lsh '+#13+
                                     'update changku_diaoru_table set diaoru_jieshouchang=@changkuname where diaoru_lsh=@changku_lsh '+#13+
                                     'update changku_diaochu_table set diaochu_diaochuchang=@changkuname where diaochu_lsh=@changku_lsh '+#13+
                                     'update changku_diaochu_table set diaochu_ToChangku=@changkuname where diaochu_lsh=@changku_lsh '+#13+
                                 'end '+#13+                                 'IF UPDATE(LsH_shengxiao) '+#13+
                                 'begin '+#13+
                                     'DECLARE @shengxiao_lsh varchar(17) '+#13+
                                     'DECLARE @shengxiao varchar(20) '+#13+
                                     'select @shengxiao_lsh=lsh_code,@shengxiao=LsH_shengxiao from inserted '+#13+
                                     'update changku_diaoru_table set diaoru_shengxiao=@shengxiao where diaoru_lsh=@shengxiao_lsh '+#13+
                                     'update changku_diaochu_table set diaochu_shengxiao=@shengxiao where diaochu_lsh=@shengxiao_lsh '+#13+
                                     'update jh_table set jh_shengxiao=@shengxiao where jh_lsh=@shengxiao_lsh '+#13+
                                 'end '+#13+                                 'IF UPDATE(LsH_kddate) '+#13+
                                 'begin '+#13+
                                     'DECLARE @kddate_lsh varchar(17) '+#13+
                                     'DECLARE @kddate datetime '+#13+
                                     'select @kddate_lsh=lsh_code,@kddate=LsH_kddate from inserted '+#13+
                                     'update changku_diaoru_table set diaoru_date=@kddate where diaoru_lsh=@kddate_lsh '+#13+
                                     'update changku_diaochu_table set diaochu_date=@kddate where diaochu_lsh=@kddate_lsh '+#13+
                                 'end '+#13+
                                   'if (@@error=0) '+#13+
                                     'begin '+#13+
                                     'COMMIT TRANSACTION '+#13+
                                     'end '+#13+
                                   'else '+#13+
                                     'begin '+#13+
                                     'ROLLBACK TRANSACTION '+#13+
                                     'end '+#13+
                                'Set Nocount Off';

这是A2表内的触发器：                    TIGGSQLSTR:='CREATE TRIGGER '+LAddTrigName+' ON dbo.changku_diaoru_table '+#13+
                                 'FOR UPDATE,INSERT '+#13+
                                 'AS '+#13+
                                  'BEGIN TRANSACTION '+#13+
                                     'DECLARE @lb varchar(30) '+#13+
                                     'DECLARE @Key numeric(20) '+#13+
                                     'DECLARE @bh varchar(30) '+#13+
                                     'DECLARE @tm varchar(30) '+#13+
                                     'DECLARE @pm varchar(100) '+#13+
                                     'DECLARE @dw varchar(10) '+#13+
                                     'DECLARE @GuiGe varchar(10) '+#13+
                                     'DECLARE @drsl numeric(10,2) '+#13+
                                     'DECLARE @drje numeric(15,3) '+#13+
                                     'DECLARE @ghs varchar(40) '+#13+
                                     'DECLARE @ckname varchar(20) '+#13+                                     'select @lb=diaoru_lb from inserted '+#13+
                                     'select @key=nodekey from inserted '+#13+
                                     'select @bh=diaoru_bh from inserted '+#13+
                                     'select @tm=diaoru_tm from inserted '+#13+
                                     'select @pm=diaoru_pm from inserted '+#13+
                                     'select @dw=diaoru_dw from inserted '+#13+
                                     'select @GuiGe=diaoru_GuiGe from inserted '+#13+
                                     'select @drsl=diaoru_zsl from inserted '+#13+
                                     'select @drje=diaoru_je from inserted '+#13+
                                     'select @ghs=diaoru_ghs from inserted '+#13+
                                     'select @ckname=diaoru_jieshouchang from inserted '+#13+                                     'DECLARE @del_lb varchar(30) '+#13+
                                     'DECLARE @del_Key numeric(20) '+#13+
                                     'DECLARE @del_bh varchar(30) '+#13+
                                     'DECLARE @del_tm varchar(30) '+#13+
                                     'DECLARE @del_pm varchar(100) '+#13+
                                     'DECLARE @del_dw varchar(10) '+#13+
                                     'DECLARE @del_GuiGe varchar(10) '+#13+
                                     'DECLARE @del_drsl numeric(10,2) '+#13+
                                     'DECLARE @del_drje numeric(15,3) '+#13+
                                     'DECLARE @del_ghs varchar(40) '+#13+
                                     'DECLARE @del_ckname varchar(20) '+#13+                                     'select @del_lb=diaoru_lb from DELETED '+#13+
                                     'select @del_key=nodekey from DELETED '+#13+
                                     'select @del_bh=diaoru_bh from DELETED '+#13+
                                     'select @del_tm=diaoru_tm from DELETED '+#13+
                                     'select @del_pm=diaoru_pm from DELETED '+#13+
                                     'select @del_dw=diaoru_dw from DELETED '+#13+
                                     'select @del_GuiGe=diaoru_GuiGe from DELETED '+#13+
                                     'select @del_drsl=diaoru_zsl from DELETED '+#13+
                                     'select @del_drje=diaoru_je from DELETED '+#13+
                                     'select @del_ghs=diaoru_ghs from DELETED '+#13+
                                     'select @del_ckname=diaoru_jieshouchang from DELETED '+#13+                                  'if @drje IS not NULL '+#13+  //当bh为空时则不执行下面的程序
                                   'begin '+#13+
                                     'declare @countghs int '+#13+
                                     'select @countghs=(select count(changku_kc_ghs) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh) '+#13+
                                     'if @countghs=0 '+#13+
                                     'begin '+#13+
                                       'insert into changku_kc_table (changku_kc_lb,nodekey,changku_kc_bh,changku_kc_tm,changku_kc_pm,changku_kc_dw,changku_kc_GuiGe,changku_kc_zsl,changku_kc_zje,changku_kc_ghs,changku_kc_ckname) values '+
                                                                  '(@lb,@key,@bh,@tm,@pm,@dw,@GuiGe,@drsl,@drje,@ghs,@ckname) '+#13+
                                       'SET @drsl=0 '+#13+
                                       'SET @drje=0 '+#13+
                                     'end '+#13+
                                   'end '+#13+
                                     'if @del_drje IS not NULL '+#13+  //当在表格中修改了某行数据
                                     'begin '+#13+
                                     //从库存表中减去修改前的数量和金额
                                       'declare @befo_kc_zsl Integer '+#13+
                                       'select @befo_kc_zsl=(select isnull(changku_kc_zsl,0.00) from changku_kc_table where changku_kc_ghs=@del_ghs and changku_kc_ckname=@del_ckname and changku_kc_bh=@del_bh) '+#13+
                                       'declare @befo_kc_zje Integer '+#13+
                                       'select @befo_kc_zje=(select isnull(changku_kc_zje,0.000) from changku_kc_table where changku_kc_ghs=@del_ghs and changku_kc_ckname=@del_ckname and changku_kc_bh=@del_bh) '+#13+
                                       'update changku_kc_table set changku_kc_zsl=@befo_kc_zsl-@del_drsl,changku_kc_zje=@befo_kc_zje-@del_drje where changku_kc_ghs=@del_ghs and changku_kc_ckname=@del_ckname and changku_kc_bh=@del_bh '+#13+
                                      //将修改后的数量和金额+之前库存的数量和金额写入库存表
                                       'declare @end_kc_zsl Integer '+#13+
                                       'select @end_kc_zsl=(select isnull(changku_kc_zsl,0.00) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh) '+#13+
                                       'declare @end_kc_zje Integer '+#13+
                                       'select @end_kc_zje=(select isnull(changku_kc_zje,0.000) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh) '+#13+
                                       'update changku_kc_table set changku_kc_zsl=@end_kc_zsl+@drsl,changku_kc_zje=@end_kc_zje+@drje where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh '+#13+
                                     'end '+#13+
                                     'else '+#13+

使用游标吧，前些日子刚写的可以参考：
CREATE TRIGGER AA ON [dbo].[TABLE1]
FOR UPDATE
AS
  DECLARE @TID INT
  DECLARE @ynn varchar(50)
  DECLARE @ynnn varchar(50)
  DECLARE @xnn varchar(50)
  DECLARE @xnnn varchar(50)
  DECLARE Employee_Cursor CURSOR FOR  SELECT tid   FROM deleted  OPEN Employee_Cursor

  FETCH NEXT FROM Employee_Cursor into @tid
     WHILE @@FETCH_STATUS = 0
  BEGIN
    select @ynn = tnn, @ynnn=tnnn from deleted where tid=@tid
    select @xnn = tnn, @xnnn=tnnn from inserted where tid=@tid
    insert into table2  (ynn, xnn, ynnn,xnnn) values (@ynn, @xnn, @ynnn, @xnnn)
    FETCH NEXT FROM Employee_Cursor into @tid
  END  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor

'begin '+#13+//当在表格中新增一行数据时
                                    //将修改完毕后的入库数量及金额数据更新到库存表中
                                       'declare @kc_zsl Integer '+#13+
                                       'select @kc_zsl=(select isnull(changku_kc_zsl,0.00) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh) '+#13+
                                       'declare @kc_zje Integer '+#13+
                                       'select @kc_zje=(select isnull(changku_kc_zje,0.000) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh) '+#13+
                                       'update changku_kc_table set changku_kc_zsl=@kc_zsl+@drsl,changku_kc_zje=@kc_zje+@drje where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh '+#13+
                                      'end '+#13+
                                   'if (@@error=0) '+#13+
                                     'begin '+#13+
                                     'COMMIT TRANSACTION '+#13+
                                     'end '+#13+
                                   'else '+#13+
                                     'begin '+#13+
                                     'ROLLBACK TRANSACTION '+#13+
                                     'end';

不好意思耽误大家时间了，代码有点乱。我重新放一段：
A1表触发器：
CREATE TRIGGER UPDATE_lsh_ghs_changku ON dbo.lsh_table
FOR UPDATE
AS
Set Nocount On
BEGIN TRANSACTION
IF UPDATE (LsH_ghs)
begin
DECLARE @lsh varchar(17)
DECLARE @ghs varchar(40)
select @lsh=lsh_code,@ghs=lsh_ghs from inserted
update changku_diaoru_table set diaoru_ghs=@ghs where diaoru_lsh=@lsh
update changku_diaochu_table set diaochu_ghs=@ghs where diaochu_lsh=@lsh
end
IF UPDATE(LsH_changku)
begin
DECLARE @changku_lsh varchar(17)
DECLARE @changkuname varchar(20)
select @changku_lsh=lsh_code,@changkuname=LsH_changku from inserted
update changku_diaoru_table set diaoru_formChangku=@changkuname where diaoru_lsh=@changku_lsh
update changku_diaoru_table set diaoru_jieshouchang=@changkuname where diaoru_lsh=@changku_lsh
update changku_diaochu_table set diaochu_diaochuchang=@changkuname where diaochu_lsh=@changku_lsh
update changku_diaochu_table set diaochu_ToChangku=@changkuname where diaochu_lsh=@changku_lsh
end
IF UPDATE(LsH_shengxiao)
begin
DECLARE @shengxiao_lsh varchar(17)
DECLARE @shengxiao varchar(20)
select @shengxiao_lsh=lsh_code,@shengxiao=LsH_shengxiao from inserted
update changku_diaoru_table set diaoru_shengxiao=@shengxiao where diaoru_lsh=@shengxiao_lsh
update changku_diaochu_table set diaochu_shengxiao=@shengxiao where diaochu_lsh=@shengxiao_lsh
update jh_table set jh_shengxiao=@shengxiao where jh_lsh=@shengxiao_lsh
end
IF UPDATE(LsH_kddate)
begin
DECLARE @kddate_lsh varchar(17)
DECLARE @kddate datetime
select @kddate_lsh=lsh_code,@kddate=LsH_kddate from inserted
update changku_diaoru_table set diaoru_date=@kddate where diaoru_lsh=@kddate_lsh
update changku_diaochu_table set diaochu_date=@kddate where diaochu_lsh=@kddate_lsh
end
if (@@error=0)
begin
COMMIT TRANSACTION
end
else
begin
ROLLBACK TRANSACTION
end
Set Nocount OffA2表触发器：
CREATE TRIGGER add_changku_kc ON dbo.changku_diaoru_table
FOR UPDATE,INSERT
AS
BEGIN TRANSACTION
DECLARE @lb varchar(30)
DECLARE @Key numeric(20)
DECLARE @bh varchar(30)
DECLARE @tm varchar(30)
DECLARE @pm varchar(100)
DECLARE @dw varchar(10)
DECLARE @GuiGe varchar(10)
DECLARE @drsl numeric(10,2)
DECLARE @drje numeric(15,3)
DECLARE @ghs varchar(40)
DECLARE @ckname varchar(20)
select @lb=diaoru_lb from inserted
select @key=nodekey from inserted
select @bh=diaoru_bh from inserted
select @tm=diaoru_tm from inserted
select @pm=diaoru_pm from inserted
select @dw=diaoru_dw from inserted
select @GuiGe=diaoru_GuiGe from inserted
select @drsl=diaoru_zsl from inserted
select @drje=diaoru_je from inserted
select @ghs=diaoru_ghs from inserted
select @ckname=diaoru_jieshouchang from inserted
DECLARE @del_lb varchar(30)
DECLARE @del_Key numeric(20)
DECLARE @del_bh varchar(30)
DECLARE @del_tm varchar(30)
DECLARE @del_pm varchar(100)
DECLARE @del_dw varchar(10)
DECLARE @del_GuiGe varchar(10)
DECLARE @del_drsl numeric(10,2)
DECLARE @del_drje numeric(15,3)
DECLARE @del_ghs varchar(40)
DECLARE @del_ckname varchar(20)
select @del_lb=diaoru_lb from DELETED
select @del_key=nodekey from DELETED
select @del_bh=diaoru_bh from DELETED
select @del_tm=diaoru_tm from DELETED
select @del_pm=diaoru_pm from DELETED
select @del_dw=diaoru_dw from DELETED
select @del_GuiGe=diaoru_GuiGe from DELETED
select @del_drsl=diaoru_zsl from DELETED
select @del_drje=diaoru_je from DELETED
select @del_ghs=diaoru_ghs from DELETED
select @del_ckname=diaoru_jieshouchang from DELETED
if @drje IS not NULL
begin
declare @countghs int
select @countghs=(select count(changku_kc_ghs) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh)
if @countghs=0
begin
insert into changku_kc_table (changku_kc_lb,nodekey,changku_kc_bh,changku_kc_tm,changku_kc_pm,changku_kc_dw,changku_kc_GuiGe,changku_kc_zsl,changku_kc_zje,changku_kc_ghs,changku_kc_ckname) values (@lb,@key,@bh,@tm,@pm,@dw,@GuiGe,@drsl,@drje,@ghs,@ckname)
SET @drsl=0
SET @drje=0
end
end
if @del_drje IS not NULL
begin
declare @befo_kc_zsl Integer
select @befo_kc_zsl=(select isnull(changku_kc_zsl,0.00) from changku_kc_table where changku_kc_ghs=@del_ghs and changku_kc_ckname=@del_ckname and changku_kc_bh=@del_bh)
declare @befo_kc_zje Integer
select @befo_kc_zje=(select isnull(changku_kc_zje,0.000) from changku_kc_table where changku_kc_ghs=@del_ghs and changku_kc_ckname=@del_ckname and changku_kc_bh=@del_bh)
update changku_kc_table set changku_kc_zsl=@befo_kc_zsl-@del_drsl,changku_kc_zje=@befo_kc_zje-@del_drje where changku_kc_ghs=@del_ghs and changku_kc_ckname=@del_ckname and changku_kc_bh=@del_bh
declare @end_kc_zsl Integer
select @end_kc_zsl=(select isnull(changku_kc_zsl,0.00) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh)
declare @end_kc_zje Integer
select @end_kc_zje=(select isnull(changku_kc_zje,0.000) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh)
update changku_kc_table set changku_kc_zsl=@end_kc_zsl+@drsl,changku_kc_zje=@end_kc_zje+@drje where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh
end
else
begin
declare @kc_zsl Integer
select @kc_zsl=(select isnull(changku_kc_zsl,0.00) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh)
declare @kc_zje Integer
select @kc_zje=(select isnull(changku_kc_zje,0.000) from changku_kc_table where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh)
update changku_kc_table set changku_kc_zsl=@kc_zsl+@drsl,changku_kc_zje=@kc_zje+@drje where changku_kc_ghs=@ghs and changku_kc_ckname=@ckname and changku_kc_bh=@bh
end
if (@@error=0)
begin
COMMIT TRANSACTION
end
else
begin
ROLLBACK TRANSACTION
end

程序没看完,只看到前面一点,提点意见,既然你是操作一张表,条件又是相同的那么还有必要把UPDATE分开写吗?
update changku_diaoru_table set diaoru_formChangku=@changkuname where diaoru_lsh=@changku_lsh
update changku_diaoru_table set diaoru_jieshouchang=@changkuname where iaoru_lsh=@changku_lsh
为何不写成
update changku_diaoru_table
set diaoru_formChangku=@changkuname,
diaoru_jieshouchang=@changkuname
where diaoru_lsh=@changku_lsh

谢谢 fhuibo(永远深爱一个叫“莎“的好女孩儿) ，好像也不行，A2表的触发器也只触发一次。
搞了几天，被困住了。郁闷ing

触发器的事务回滚,以及触发器中的INSERT语句都要特殊处理,
除回你用很笨的方法,更新后 REQUERY

舉個例子吧, 如你上面的select @changku_lsh=lsh_code,@changkuname=LsH_changku from inserted update changku_diaoru_table set diaoru_formChangku=@changkuname where diaoru_lsh=@changku_lsh 這樣只能更改一條記錄, 如果是多記錄修改, 你這樣只能處理最後一條記錄

我在DFW上搜索了一下，发现批量更新某表，某表的deleted和insertd表只保留批量更新数据的最后一条记录(可能这是我的问题所在)，如果是，是否是SQL2000的技术问题，还是本来就是这样的，我该如何通过触发器来解决该问题，游标我再试试。同时结贴了。

调试易

MSSQL2000触发器怎么会这样？能帮看看吗？

解决方案 »