批量更新的触发器问题

最新推荐文章于 2024-08-25 17:55:11 发布

人鱼传说

最新推荐文章于 2024-08-25 17:55:11 发布

阅读量6.9k

点赞数 1

分类专栏： MS SQL管理与开发文章标签： table insert null

本文链接：https://blog.csdn.net/baronyang/article/details/5174734

版权

MS SQL管理与开发专栏收录该内容

16 篇文章 0 订阅

订阅专栏

原来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

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

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