SQL Server - 插入表 [英] SQL Server - Inserted Table

问题描述

我使用以下触发器检查inserted / deleted表的输出，如何在验证列之后将拦截的UPDATE命令传递给服务器？

I am checking the output of the inserted / deleted table using the following trigger, how can I pass on the intercepted UPDATE command to the server after verifying columns?

CREATE TRIGGER Test1_LastUpdate ON Test1
INSTEAD OF UPDATE 
AS
    SELECT * FROM Inserted
    SELECT * FROM Deleted
GO

编辑：我正在寻找一个不需要在模式更新后更改的解决方案。

I am looking for a solution that will not need to be changed after schema updates.

CREATE TRIGGER Test1_LastUpdate2 ON Test1
INSTEAD OF UPDATE
AS
    --COMMIT UPDATE THAT WAS INTERCEPTED
END

推荐答案

在验证列之后向服务器发送UPDATE命令是通过自己执行 UPDATE

The only way you "pass on the intercepted UPDATE command to the server after verifying columns" is by performing the UPDATE yourself.

但是，您现在已经说过，当这些列添加到表中时，您不需要向触发器添加更多列。因此，您有一个备用选项，可以简单地回滚任何无效的更改。这可能看起来像这样：

However, you have now said that you don't want to have to add more columns to the trigger when those columns are added to the table. So you have an alternate option of simply rolling back any change that is invalid. That might look something like this:

CREATE TRIGGER TR_Sample_U ON dbo.Sample -- No AFTER trigger needed here!
AS
IF EXISTS ( --check for disallowed modifications
   SELECT *
   FROM
      Inserted I
      INNER JOIN Deleted D
         ON I.SampleID = D.SampleID
   WHERE
      I.Something <> D.Something
      AND I.UpdateDate = D.UpdateDate
)
ROLLBACK TRAN;

选项2 - 在触发器中执行UPDATE

但是，如果您需要更多地控制更新实际所需的内容，例如需要在提交之前修改值，则必须自行执行更新。例如：

Option 2 - Perform UPDATE in trigger

However, if you need more control over what the update actually entails, such as needing to modify a value before it is committed, you would have to perform the update yourself. For example:

CREATE TRIGGER TR_Sample_U ON dbo.Sample
INSTEAD OF UPDATE
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

UPDATE S
SET S.Value = I.Value + '+'
FROM
   dbo.Sample S
   INNER JOIN Inserted I
      ON S.SampleID = I.SampleID
;

这是一个简单的例子，不做任何检查，但你得到的想法 - 当您对 Sample 表执行更新，您将看到该值获取了额外的 + 字符 - 您的更新被截获，并且在提交之前修改了插入的值（表示更新后的建议更改）。

This is a trivial example that doesn't do any checking, but you get the idea--when you perform an update on the Sample table, you will see that the value acquires an extra + character--your update was intercepted and the Inserted value (representing the proposed change after update) was modified before being committed.

查看此操作中的SQL Fiddle演示。

唯一需要注意的是递归：

The only thing to watch out for is recursion:

1. 直接递归

1. Direct Recursion

当您的更新可能导致运行修改同一基表的其他触发器时，您可以在它们之间进行乒乓，直到达到最大嵌套级别，并且整个事务回滚。

When your update could cause other triggers to run that modify the same base table--then you can get ping-ponging between them, until the maximum nest level is reached and the entire transaction is rolled back. So be aware of possible ping-ponging between triggers.

间接递归

不必担心这一点，因为数据库级 RECURSIVE TRIGGERS 选项在SQL Server中默认为关闭。

You probably don't have to worry about this one, because the database-level RECURSIVE TRIGGERS option is off by default in SQL Server. However, if it is on, you can get the same trigger firing based on the new update.

这些都可以改进by，variously：

These be ameliorated by, variously:

• 在触发器中检查 TRIGGER_NESTLEVEL

要避免直接递归，请结合触发器。

To avoid direct recursion only, combine the triggers.

在某些特定情况下，策略性地指定哪个触发器将首先/最后运行可以解决问题。您不能指定绝对顺序，但可以选择第一个和最后一个。

In some particular cases strategically assigning which trigger will run first/last may fix the problem. You can't specify absolute order, but you can choose the first one and the last one.

请注意，乒乓问题适用于修改其自己的基表的任何类型的触发器 INSTEAD OF 或 AFTER 涉及通过另一个表（具有修改另一个表的触发器）的更新链。最终返回修改基表。

Note that the ping-pong problem applies to any type of trigger, INSTEAD OF or AFTER, that modifies its own base table, or is involved in a chain of updates through another table (that has a trigger that modifies another table ...) that eventually comes back to modify the base table.

我称之为选项2B，因为它是真正的选项2，但增强。如果你不想在每次向表中添加列时都手动更新触发器（我完全同意这种观点），你可以自动执行。创建一个存储过程，可以创建一个适当的触发器，以观察您需要的所有验证。您可以将此验证的基本代码放入表中，然后在SP中将其选择为变量，在SQL脚本中添加更新最后更新的列，通过挖掘 INFORMATION_SCHEMA.COLUMNS view，然后最后重写触发器。此外，还可以将其附加到DDL触发器，以使其100％自动化：您可以从基表中添加或删除列，触发DDL触发器，并为您重写DML触发器。

I call this option 2B because it is really option 2, but with an enhancement. If you don't want to have to manually update the trigger every time you add columns to the table (a sentiment I wholly agree with) you could automate this. Create a stored procedure that can create an appropriate trigger that observes all the validation that you need. You could put the base code for this validation into a table, then in the SP select it into variables, add in the SQL script updating the columns for the final update by mining the information in the INFORMATION_SCHEMA.COLUMNS view, then finally rewrite the trigger. This could furthermore be attached to a DDL trigger, so that it is 100% automated: you would add or remove a column from the base table, the DDL trigger would fire, and rewrite the DML trigger for you.

这听起来像很多工作，但是如果你设计为数据驱动，它可以推广到整个数据库中的任何表，这可能是非常有价值的，取决于您的使用方案。

That sounds like a lot of work, but if you designed it to be data-driven it could be generalized to work with any table in your entire database, which could be of great value, depending on your usage scenarios.

这篇关于SQL Server - 插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！