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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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值只有第一条记录更新了,下面改一下触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

  

更新触发器后,一些正常。

总结:为了保险起见,update触发器还是要用游标来处理,才能保证全部触发执行.

批量更新数据小心SQL触发器的陷阱,布布扣,bubuko.com

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

上一篇:java date 到 Oracle date 精确到时分秒


下一篇:优化DB2缓冲页的大小