批量更新数据小心SQL触发器的陷阱

浏览数：21 / 时间：2015年06月12日

批量更新数据时候，Inserted和Deleted临时表也是批量的，但触发器只会调用执行一次！两个概念千万不要弄混淆！

错误的理解；例如：创建在A表上创建了一个Update触发器，里面写的是Update一条记录；当在A表进行update数据时候，会调用执行触发器一次，不可能因为触发器中有Update语句会又执行一次，那样岂不是死循环了。。

本文章转载：http://blog.csdn.net/baronyang/article/details/5174734

原来update触发器只会被update触发一次，无论更新的记录数是多少，以下为验证代码

CREATE TABLE TMP_TABLE1 (PersonCode VARCHAR(20) NOT 
NULL PRIMARY KEY,[NAME] VARCHAR(50) NULL,DepCode VARCHAR(20) NULL,Amount MONEY null)
CREATE TABLE TMP_TABLE2 (PersonCode VARCHAR(20) NOT 
NULL PRIMARY KEY,[NAME] VARCHAR(50) NULL,DepCode VARCHAR(20) NULL,Amount MONEY null)
 
GO
INSERT INTO TMP_TABLE1(PersonCode,[NAME],Amount)
VALUES(‘00001‘,‘AAA1‘,1000)
INSERT INTO TMP_TABLE1(PersonCode,[NAME],Amount)
VALUES(‘00002‘,‘AAA2‘,1000)
INSERT INTO TMP_TABLE1(PersonCode,[NAME],Amount)
VALUES(‘00003‘,‘AAA3‘,1000)
INSERT 
INTO TMP_TABLE1(PersonCode,[NAME],Amount)
VALUES(‘00004‘,‘AAA4‘,1000)
INSERT 
INTO TMP_TABLE1(PersonCode,[NAME],Amount)
VALUES(‘00005‘,‘AAA5‘,1000)
 
INSERT 
INTO TMP_TABLE2(PersonCode,[NAME],Amount)
VALUES(‘00001‘,‘AAA1‘,1000)
INSERT 
INTO TMP_TABLE2(PersonCode,[NAME],Amount)
VALUES(‘00002‘,‘AAA2‘,1000)
INSERT 
INTO TMP_TABLE2(PersonCode,[NAME],Amount)
VALUES(‘00003‘,‘AAA3‘,1000)
INSERT 
INTO TMP_TABLE2(PersonCode,[NAME],Amount)
VALUES(‘00004‘,‘AAA4‘,1000)
INSERT 
INTO TMP_TABLE2(PersonCode,[NAME],Amount)
VALUES(‘00005‘,‘AAA5‘,1000)
 
GO
Alter TRIGGER dbo.tmp_table1_update ON 
tmp_table1
AFTER UPDATE
AS
SELECT 
* INTO #INS FROM INSERTED
DECLARE 
@PersonCode VARCHAR(20),@Amount MONEY
IF UPDATE(Amount)
BEGIN
  SELECT 
@personcode=personcode,@Amount=Amount FROM 
#INS
  UPDATE 
TMP_TABLE2 SET 
Amount =@Amount WHERE 
PersonCode=@PersonCode    
END
 
GO
 
UPDATE 
TMP_TABLE1 SET 
Amount = 2000
select 
* from TMP_TABLE1
SELECT 
* FROM TMP_TABLE2

然后再更新,发现TMP_TABLE1的Amount字段值都更新为2000，但TMP_TABLE2的Amount值只有第一条记录更新了，下面改一下触发器

Alter TRIGGER dbo.tmp_table1_update ON 
tmp_table1
AFTER UPDATE
AS
SELECT * INTO #INS FROM INSERTED
DECLARE @PersonCode VARCHAR(20),@Amount MONEY
IF UPDATE(Amount)
BEGIN
    DECLARE 
AmountCursor CURSOR 
FOR
    SELECT 
personcode,Amount FROM 
#INS
    OPEN 
AmountCursor
    FETCH 
NEXT FROM AmountCursor INTO 
@PersonCode,@Amount
    WHILE @@FETCH_STATUS=0
    BEGIN
        UPDATE 
TMP_TABLE2 SET 
Amount =@Amount WHERE 
PersonCode=@PersonCode
        FETCH 
NEXT FROM AmountCursor INTO 
@PersonCode,@Amount
    END
    CLOSE 
AmountCursor
    DEALLOCATE 
AmountCursor         
END