原文連結:http://blog.csdn.net/baronyang/article/details/5174734
原來update觸發器隻會被update觸發一次,無論更新的記錄數是多少,以下為驗證代碼
[php] view plaincopy
- 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)
- VALUES('00002','AAA2',1000)
- VALUES('00003','AAA3',1000)
- VALUES('00004','AAA4',1000)
- VALUES('00005','AAA5',1000)
- INSERT INTO TMP_TABLE2(PersonCode,[NAME],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
- SELECT @personcode=personcode,@Amount=Amount FROM #INS
- UPDATE TMP_TABLE2 SET Amount =@Amount WHERE PersonCode=@PersonCode
- END
- UPDATE TMP_TABLE1 SET Amount = 2000
- select * from TMP_TABLE1
- SELECT * FROM TMP_TABLE2
然後再更新,發現TMP_TABLE1的Amount字段值都更新為2000,但TMP_TABLE2的Amount值隻有第一條記錄更新了,下面改一下觸發器
- DECLARE AmountCursor CURSOR FOR
- SELECT personcode,Amount FROM #INS
- OPEN AmountCursor
- FETCH NEXT FROM AmountCursor INTO @PersonCode,@Amount
- WHILE @@FETCH_STATUS=0--0為Fetch語句已成功,-1為Fetch語句失敗或不在結果集中,-2提取的行不存在
- BEGIN
- UPDATE TMP_TABLE2 SET Amount =@Amount WHERE PersonCode=@PersonCode
- FETCH NEXT FROM AmountCursor INTO @PersonCode,@Amount
- END
- CLOSE AmountCursor
- DEALLOCATE AmountCursor