SQL Server更新仅在其更新时触发，并且仅针对列中的特定值

There is a certain job that will insert and update this table called ContactInfo (with 2 columns - Id, EmailId) several times a day.

有一项工作将每天多次插入和更新名为ContactInfo的表(包含2列--Id,EmailId)。

What's a good way to write a trigger on this table to revert back the EmailId for only specific Ids, whenever only those EmailIds for those Ids get updated?

只有那些ID的EmailId得到更新时,在这个表上写一个触发器以便仅为特定ID恢复EmailId的好方法是什么?

Don't mind hard-coding those Ids in the trigger since the list is about 40.

不要介意在触发器中对这些ID进行硬编码,因为列表大约是40。

But specifically concerned about trigger not firing for every update, since updates happen all the time, and don't want the trigger to cause resource issues.

但是特别关注触发器不会为每次更新触发,因为更新一直在发生,并且不希望触发器导致资源问题。

Additional info: table has about 600k entries and is indexed on Id.

附加信息:表有大约600k条目,并在Id上编入索引。

In summary: is it possible for the trigger to get fired only when certain values are updated in the column, and not any update on the column.

总结:只有在列中更新某些值时才触发触发器,而不是列上的任何更新。

1 个解决方案

#1

One alternative mechanism you might consider would be adding another table, called, say, LockedValues. I'm a bit unsure from your narrative what values you're trying to prevent changes to, but here's an example:

您可能考虑的另一种替代机制是添加另一个表,称为LockedValues。我从你的叙述中有点不确定你试图阻止改变的价值,但这里有一个例子:

Table T, contains two columns, ID and Val:

表T包含两列,ID和Val:

create table T (
    ID int not null,
    Val int not null,
    constraint PK_T PRIMARY KEY (ID),
    constraint UK_T_Lockable UNIQUE (ID,Val)
)

And we have 3 rows:

我们有3排:

insert into T(ID,Val)
select 1,10 union all
select 2,20 union all
select 3,30

And we want to prevent the row with ID 2 from having it's Val changed:

我们希望阻止ID为2的行更改Val:

create table Locked_T (
    ID int not null,
    Val int not null,
    constraint UQ_Locked_T UNIQUE (ID,Val), --Only really need an index here
    constraint FK_Locked_T_T FOREIGN KEY (ID,Val) references T (ID,Val)
)
insert into Locked_T (ID,Val) select 2,20

And so now, of course, any application that is only aware of T will be unable to edit the row with ID 2, but can freely alter rows 1 and 3.

所以现在,当然,任何只知道T的应用程序都无法编辑ID为2的行,但可以*地改变第1行和第3行。

This has the benefit that the enforcement code is built into SQL Server already, so probably quite efficient. You don't need a unique key on Locked_T, but it should be indexed so that it's quite quick to detect that values aren't present.

这样做的好处是执行代码已经内置到SQL Server中,因此可能非常有效。您不需要Locked_T上的唯一键,但应对其进行索引,以便能够快速检测到不存在的值。

This all assumes that you were going to write a trigger that rejected changes, rather than one that reverted changes. For that, you'd still have to write a trigger (though I'd still suggest having this separate table, and then writing your trigger to do an update inner joining inserted with Locked_T - which should be quite efficient still).

这一切都假设您要编写一个拒绝更改的触发器,而不是一个还原更改的触发器。为此,你仍然需要编写一个触发器(虽然我仍然建议使用这个单独的表,然后编写你的触发器以使用Locked_T进行更新内部连接 - 这应该是非常有效的)。

(Be warned, however: Triggers that revert changes are evil)

(但要注意:恢复变化的触发器是邪恶的)

#1