SQL INSTEAD OF UPDATE 触发器 - 这可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9852394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSTEAD OF UPDATE Trigger - is this possible?
提问by bigtv
I am making some tweaks to a legacy application built on SQL Server 2000, needless to say I only want to do the absolute minimum in the fear that it may just all fall apart.
我正在对构建在 SQL Server 2000 上的遗留应用程序进行一些调整，不用说我只想做绝对最少的事情，因为担心它可能会全部崩溃。
I have a large table of users, tbUsers, with a BIT flag for IsDeleted. I want to archive off all current and future IsDeleted = 1 user records into my archive table tbDeletedUsers.
我有一个很大的用户表 tbUsers，带有一个用于 IsDeleted 的 BIT 标志。我想将所有当前和未来的 IsDeleted = 1 用户记录归档到我的归档表 tbDeletedUsers 中。
Moving the currently deleted users is straight forward, however I want a way to move any future users where the IsDeleted flag is set. I could use a standard AFTER trigger on the column however I plan to add some constraints to the tbUser table that would violate this, what I'd like is for my INSTEAD OF UPDATE trigger to fire and move the record to archive table instead?
移动当前删除的用户很简单，但是我想要一种方法来移动设置了 IsDeleted 标志的任何未来用户。我可以在列上使用标准的 AFTER 触发器，但是我计划向 tbUser 表添加一些会违反这一点的约束，我希望我的 INSTEAD OF UPDATE 触发器触发并将记录移动到存档表？
I guess my question is... is it possible to trigger an INSTEAD OF UPDATE trigger on the update of an individual column? This is what I have so far:
我想我的问题是...是否可以在更新单个列时触发 INSTEAD OF UPDATE 触发器？这是我到目前为止：
CREATE TRIGGER trg_ArchiveUsers
INSTEAD OF UPDATE ON tbUsers
AS
BEGIN
...
END
GO
If so an example (SQL 2000 compatible) would be much appreciated!
如果是这样一个例子（SQL 2000 兼容）将不胜感激！
采纳答案by Andriy M
Using the UPDATE(columnname)test, you can check ina trigger whether a specific column was updated (and then take specific actions), but you can't have a trigger fireonly on the update of a specific column. It will fire as soon as the update is performed, regardless of the fact which column was the target of the update.
使用测试，您可以检查在触发特定的列是否被更新（再采取具体行动），但你不能有一个触发火灾仅在特定列的更新。它会在执行更新后立即触发，无论哪个列是更新的目标。UPDATE(columnname)
So, if you think you have to use an INSTEAD OF UPDATEtrigger, you'll need to implement two kinds of actions in it:
因此，如果您认为必须使用INSTEAD OF UPDATE触发器，则需要在其中实现两种操作：
1) insert into tbDeletedUsers+ delete from tbUsers– when IsDeletedis updated (or, more exactly, updated andset to 1);
1) insert into tbDeletedUsers+ delete from tbUsers– 何时IsDeleted更新（或更准确地说，更新并设置为1）；
2) update tbUsersnormally – when IsDeletedis not updated (or updated but not set to 1).
2)tbUsers正常更新——何时IsDeleted未更新（或已更新但未设置为1）。
Because more than one row can be updated with a single UPDATEinstruction, you might also need to take into account that some rows might have IsDeletedset to 1and others not.
因为可以使用一条UPDATE指令更新多行，您可能还需要考虑某些行可能已IsDeleted设置为 ，1而其他行则没有。
I'm not a big fan of INSTEAD OFtriggers, but if I really had to use one for a task like yours, I might omit the UPDATE()test and implement the trigger like this:
我不是INSTEAD OF触发器的忠实粉丝，但是如果我真的必须将触发器用于像您这样的任务，我可能会省略UPDATE()测试并像这样实现触发器：
CREATE TRIGGER trg_ArchiveUsers
ON tbUsers
INSTEAD OF UPDATE
AS
BEGIN
UPDATE tbUsers
SET
column = INSERTED.column,
…
FROM INSERTED
WHERE INSERTED.key = tbUsers.key
AND INSERTED.IsDeleted = 0
;
DELETE FROM tbUsers
FROM INSERTED
WHERE INSERTED.key = tbUsers.key
AND INSERTED.IsDeleted = 1
;
INSERT INTO tbDeletedUsers (columns)
SELECT columns
FROM INSERTED
WHERE IsDeleted = 1
;
END
