原来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)
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)
creater 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 WHERE
END
UPDATE TMP_TABLE1 SET Amount = 2000
select * from TMP_TABLE1
SELECT * FROM TMP_TABLE2
然后再更新,发现TMP_TABLE1的Amount字段值都更新为2000，但TMP_TABLE2的Amount值只有{dy}条记录更新了，下面改一下触发器
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 WHERE
FETCH NEXT FROM AmountCursor INTO @PersonCode,@Amount
END
CLOSE AmountCursor
DEALLOCATE AmountCursor
END
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 WHERE
FETCH NEXT FROM AmountCursor INTO @PersonCode,@Amount
END
CLOSE AmountCursor
DEALLOCATE AmountCursor
END
更新触发器后，一些正常。
总结：为了保险起见，update触发器还是要用游标来处理，才能保证全部触发执行.
以下方法也可以参考：
要考虑 单行和多行的并发处理吧
CREATE TRIGGER [AAAA] ON [dbo].[AA]
FOR INSERT, UPDATE
AS
if update(e0101)
begin
IF @@ROWCOUNT = 1
begin
update a01 set c0105=mc0000 from a01,e01,inserted
where a01.e0101=e01.e0101 and inserted.a0188=a01.a0188
end
else
begin
update a01 set c0105=mc0000 from a01,e01,inserted
where a01.e0101=e01.e0101 and a01.a0188 in (select a0188 from inserted)
end
end
