检查触发器中是否发生了更改
检查触发器中是否发生了更改
提问于 2010-08-16 22:57:55
我需要监控表上字段的子集，并在其中一个字段发生更改时执行任务。
我在表update上使用触发器，然后按如下方式查看更改：
-- join the deleted and inserted to get a full list of rows
select * into #tmp from (select * from inserted union select * from deleted) un
-- select a count of differing rows, > 1 means something is different
select distinct count(*) from #tmp这很好，计数为2或更多意味着在单行更新上有所不同。问题是，如果我正在进行多行更新，那么它就会崩溃。
有没有办法让它适用于多行更新，或者我需要尝试一种完全不同的方法。
回答 2
Stack Overflow用户
回答已采纳
发布于 2010-08-17 16:41:59
我最终得到了一个相当简单的解决方案。我在check周围编写了一个额外的循环，用于对插入的每一行执行检查。
-- get a list of updated line id's
select field1 as id into #loop from inserted
-- loop through all the id's and do a compare
while (select count(*) from #loop) > 0 begin
select top 1 @id = id from #loop
select * into #tmp from (select * from inserted where field1 = @id union
select * from deleted where field1 = @id) un
-- do a select ditinct to count the differing lines.
if (select distinct count(*) from #tmp) > 1 begin
-- the 2 lines don't match, so mark for update
update test1 set flag = 1 where field1 = @id
end
drop table #tmp
delete #loop where id = @id
endStack Overflow用户
发布于 2010-08-16 23:01:20
你可以这样做(语法完全未经测试)
IF NOT UPDATE(col)
RETURN
SELECT inserted.key, inserted.col as i_col, deleted.col as d_col
INTO #interestingrows
FROM inserted JOIN deleted on inserted.key = deleted.key
and inserted.col <> deleted.col /*If col is nullable cater for that as well*/
IF @@ROWCOUNT=0
RETURN
/*Process contents of #interestingrows*/页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3494355
复制相关文章
相似问题
腾讯云开发者
