之前写过一个批处理,当此表有数据更新则同时写入到bak表,但是今天发现当批量更新此表时此触发器就不Work了
报数据不是唯一的请各位大神帮忙看看如何修改此触发器
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER trigger [AccountUpdate] ON [dbo].[account]
for update
as
beginupdate AccountBak set [NAME]=(select [NAME] from inserted)
,[OwnerID]=(select [OwnerID] from inserted)
,[Industry]=(select [Industry] from inserted)
,[AccountType]=(select [ACCOUNTTYPE__C] from inserted)
,[BusinessUnit]=(select [BUSINESSUNIT__C] from inserted)
,[Territory]=(select [TERRITORY__C] from inserted)
,[IsActive]=(select [ISACTIVE__C] from inserted)
,[ISDELETED]=(select [ISDELETED] from inserted) where ID=(select ID from inserted)
end
报数据不是唯一的请各位大神帮忙看看如何修改此触发器
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER trigger [AccountUpdate] ON [dbo].[account]
for update
as
beginupdate AccountBak set [NAME]=(select [NAME] from inserted)
,[OwnerID]=(select [OwnerID] from inserted)
,[Industry]=(select [Industry] from inserted)
,[AccountType]=(select [ACCOUNTTYPE__C] from inserted)
,[BusinessUnit]=(select [BUSINESSUNIT__C] from inserted)
,[Territory]=(select [TERRITORY__C] from inserted)
,[IsActive]=(select [ISACTIVE__C] from inserted)
,[ISDELETED]=(select [ISDELETED] from inserted) where ID=(select ID from inserted)
end
这时你的触发器需要写 循环(游标或 while)来获取值然后更新
不知道是否work,测试完成之后,代码会贴上来
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [AccountUpdate] ON [dbo].[test]
for update
as
beginupdate accountbak
set [NAME]=ins.[NAME]
,[OwnerID]=ins.[OwnerID]
,[Industry]=ins.[Industry]
,[AccountType]=ins.[ACCOUNTTYPE__C]
,[BusinessUnit]=ins.[BUSINESSUNIT__C]
,[Territory]=ins.[TERRITORY__C]
,[IsActive]=ins.[ISACTIVE__C]
,[ISDELETED]=ins.[ISDELETED]
,PROVINCE__C=ins.PROVINCE__C
,CITY__C=ins.PROVINCE__C
FROM AccountBak ab, INSERTED ins
WHERE ab.id = ins.id
end