如何在 INSTEAD OF UPDATE 触发器中检查列是否正在更新 [英] How to check if a column is being updated in an INSTEAD OF UPDATE Trigger

问题描述

我正在对构建在 SQL Server 2000 上的遗留应用程序进行一些调整，不用说我只想做绝对最少的事情，因为担心它可能会全部崩溃.

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.

我有一个很大的用户表 tbUsers，带有一个用于 IsDeleted 的 BIT 标志.我想将所有当前和未来的 IsDeleted = 1 用户记录归档到我的归档表 tbDeletedUsers 中.

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.

移动当前删除的用户很简单，但是我想要一种方法来移动设置了 IsDeleted 标志的任何未来用户.我可以在列上使用标准的 AFTER 触发器，但是我计划向 tbUser 表添加一些会违反这一点的约束，我希望我的 INSTEAD OF UPDATE 触发器触发并将记录移动到存档表?

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?

我想我的问题是...是否可以在更新单个列时触发 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:

 CREATE TRIGGER trg_ArchiveUsers
 INSTEAD OF UPDATE ON tbUsers
 AS 
    BEGIN
      ...
    END
 GO

如果是这样一个例子(SQL 2000 兼容)将不胜感激！

If so an example (SQL 2000 compatible) would be much appreciated!

推荐答案

使用 UPDATE(columnname) 测试，可以检查in一个触发器是否特定列已更新(然后执行特定操作)，但您不能仅在特定列更新时触发.它会在执行更新后立即触发，无论哪个列是更新的目标.

Using the UPDATE(columnname) test, you can check in a trigger whether a specific column was updated (and then take specific actions), but you can't have a trigger fire only 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.

因此，如果您认为必须使用 INSTEAD OF UPDATE 触发器，则需要在其中实现两种操作:

So, if you think you have to use an INSTEAD OF UPDATE trigger, you'll need to implement two kinds of actions in it:

1) 插入 tbDeletedUsers + 从 tbUsers 删除 - 当 IsDeleted 更新时(或者，更准确地说，更新 and 设置为 1);

1) insert into tbDeletedUsers + delete from tbUsers – when IsDeleted is updated (or, more exactly, updated and set to 1);

2) 正常更新 tbUsers - 当 IsDeleted 未更新(或更新但未设置为 1)时.

2) update tbUsers normally – when IsDeleted is not updated (or updated but not set to 1).

因为可以使用单个 UPDATE 指令更新多行，您可能还需要考虑某些行可能将 IsDeleted 设置为 1 其他人没有.

Because more than one row can be updated with a single UPDATE instruction, you might also need to take into account that some rows might have IsDeleted set to 1 and others not.

我不是 INSTEAD OF 触发器的忠实粉丝，但如果我真的必须将一个触发器用于像您这样的任务，我可能会省略 UPDATE()像这样测试和实现触发器:

I'm not a big fan of INSTEAD OF triggers, 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:

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

这篇关于如何在 INSTEAD OF UPDATE 触发器中检查列是否正在更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！