前几天在做数据同步的时候,用到了SQL Server的触发器,想要实现的功能是这样的:
如果工程表(ProjectInfo)的记录更新,则在同步记录表(SyncLog)里插入一条新记录,用来记录某个工程记录已更新。
触发器是这样写的:
CREATE TRIGGER [Trig_ProjectInfo_Update] ON [dbo].[zb_ProjectInfo] FOR UPDATE AS DECLARE @ProjID AS INT SET @ProjID = (SELECT ID FROM INSERTED) DECLARE @Count AS INT SELECT @Count=COUNT(0) FROM zb_SyncLog WHERE Tbl=1 AND LocalID=@ProjID IF @Count > 0 INSERT INTO zb_SyncLog (LocalID,State,Tbl) VALUES (@ProjID,2,1)
当工程表一次只更新一条数据时,触发器正常运行;当一下子更新多条工程记录时,出现了如下错误:
服务器: 消息 512,级别 16,状态 1,过程 Trig_ProjectInfo_Update,行 7
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。
看来当同时更新多条语句的时候,"select id from inserted"语句返回的不是一个ID,触发器修改成下面这样,就OK啦:
CREATE TRIGGER [Trig_ProjectInfo_Update] ON [dbo].[zb_ProjectInfo]
FOR UPDATE
AS
DECLARE @Count AS INT
SELECT @Count=COUNT(0) FROM zb_SyncLog WHERE Tbl=1 AND LocalID in (SELECT ID FROM INSERTED)
IF @Count > 0
INSERT INTO zb_SyncLog (LocalID,State,Tbl) SELECT ID,2,1 FROM INSERTED