SQL Server BEFORE UPDATE 触发器，它在执行 UPDATE 之前在字段上添加时间戳 [英] SQL Server BEFORE UPDATE trigger which adds in a timestamp on a field before UPDATE execution

问题描述

你好，stackoverflow 的好人，

Hi good people of stackoverflow,

我正在为 SQL SERVER 2008 R2 上的表开发一个触发器，用于审计目的，它应该在发送 UPDATE 查询以供执行之前为 UPDATE_TS 字段添加时间戳.结果是更新发生时带有要在查询时更新的原始值加上触发器设置的 UPDATE_TS 的附加值.

I am working on a trigger for a table on SQL SERVER 2008 R2 for auditing purpose which should add in a timestamp for the UPDATE_TS field before the UPDATE query is sent for execution. The result being that the update occurs with the original values to update on query plus the additional value of the UPDATE_TS as set by the trigger.

我也编辑了这个问题，因为我听说与不使用它们相比，内部连接在触发器的性能方面并不是很重要.我不确定这是否会增加触发器的额外开销，而不是避免触发器中的内部联接.

I have as well edited this question since I hear that inner-joins are not very heavy in terms of performance on triggers in comparison to not using them. I am not sure if this will add an additional overhead on the trigger as opposed to avoiding inner join in the trigger.

我正在处理的示例如下.感谢您的任何帮助和建议！

The example I am working on is below. Thank you for any help and suggestions!

示例表称为 MY_TABLE:

Example Table is called MY_TABLE:

CREATE TABLE [myschema].[MY_TABLE](
[MY_TABLE_ID] [bigint] IDENTITY(1,1) NOT NULL,
[FIELD_TO_UPDATE] [varchar](255) NOT NULL,
[CREATE_TS] [datetime] NULL,
[UPDATE_TS] [datetime] NULL),
PRIMARY KEY (MY_TABLE_ID))

触发创建:

CREATE TRIGGER [myschema].[my_table_update_ts_trigger] ON [mydb].[myschema].[MY_TABLE]
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE INTO MY_TABLE ([FIELD_TO_UPDATE],[UPDATE_TS])
    SELECT ins.FIELD_TO_UPDATE, GETDATE() FROM INSERTED as ins
END

推荐答案

您需要确定需要更新的行，并使用联接或半联接来完成此操作.它不会比这更有效，除非您根本不执行更新:

You need to identify the row(s) you need to update, and you do this with a join or semi-join. It's not going to get much more efficient than this, unless you simply don't perform the update at all:

CREATE TRIGGER [myschema].[my_table_update_ts_trigger] 
ON [myschema].[MY_TABLE]
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE t SET 
      FIELD_TO_UPDATE = i.FIELD_TO_UPDATE,
      UPDATE_TS = CURRENT_TIMESTAMP
    FROM myschema.MY_TABLE AS t
    INNER JOIN inserted AS i
    ON t.MY_TABLE_ID = i.MY_TABLE_ID;
END
GO

这是执行计划:

因为您需要将 inserted 中的行与您的基表进行匹配，并且由于任何操作可能会更新多于一行(触发器每个语句 在 SQL Server 中，而不是像在其他一些平台中那样每行)，并且由于这不是 BEFORE 更新而是 INSTEAD OF 更新(意味着您仍然必须实际执行本来会发生的 UPDATE没有触发器)，您需要有两个表的输出才能准确执行更新.这意味着您需要 JOIN，并且不能使用 SEMI-JOIN(例如 EXISTS)，这可能仍然违反了您的古怪要求.如果您只需要更新时间戳，您可以这样做:

Since you need to match the rows in inserted to your base table, and since there may be more than one row that gets updated by any operation (triggers fire per statement in SQL Server, not per row like in some other platforms), and since this isn't a BEFORE update but an INSTEAD OF update (meaning you still have to actually perform the UPDATE that would have happened without the trigger in place), you need to have output from both tables in order to perform the update accurately. This means you need a JOIN, and you cannot use a SEMI-JOIN (e.g. EXISTS), which probably still violates your outlandish requirements anyway. If you only needed to update the timestamp, you could do this:

UPDATE t SET UPDATE_TS = CURRENT_TIMESTAMP
  FROM myschema.MY_TABLE AS t
  WHERE EXISTS (SELECT 1 FROM inserted WHERE MY_TABLE_ID = t.MY_TABLE_ID);

不幸的是，这行不通，因为 FIELD_TO_UPDATE 会丢失，而没有在适当的连接中实际拉入 inserted 伪表.

Unfortunately, that will not work, because FIELD_TO_UPDATE gets lost without actually pulling in the inserted pseudo-table in a proper join.

另一种方法是使用 CROSS APPLY，例如:

Another way is to use a CROSS APPLY, e.g.:

 UPDATE t SET 
   FIELD_TO_UPDATE = i.FIELD_TO_UPDATE,
   UPDATE_TS = CURRENT_TIMESTAMP
FROM  inserted AS i
CROSS APPLY myschema.MY_TABLE AS t
WHERE i.MY_TABLE_ID = t.MY_TABLE_ID;

它也缺少讨厌的 JOIN 关键字，但它仍在执行 JOIN.您可以看到这一点，因为执行计划是相同的:

It, too, is missing the nasty JOIN keyword, but it is still performing a JOIN. You can see this because the execution plans are identical:

现在，理论上您可以在没有连接的情况下执行此操作，但这并不意味着它的性能会更好.事实上，我毫无疑问地向您保证，即使它不包含像 JOIN 这样的四个字母的单词，效率也会降低:

Now, you can theoretically do this without a join, but that doesn't mean it will perform better. In fact I guarantee you beyond a shadow of a doubt that this will be less efficient, even though it does not contain a single four-letter word like JOIN:

    DECLARE @NOW             DATETIME = CURRENT_TIMESTAMP,
            @MY_TABLE_ID     INT,
            @FIELD_TO_UPDATE VARCHAR(255);

    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
      SELECT MY_TABLE_ID, FIELD_TO_UPDATE FROM inserted;

    OPEN c;

    FETCH NEXT FROM c INTO @FIELD_TO_UPDATE, @MY_TABLE_ID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
      UPDATE myschema.MY_TABLE SET 
          FIELD_TO_UPDATE = @FIELD_TO_UPDATE,
                UPDATE_TS = @NOW
        WHERE MY_TABLE_ID = @MY_TABLE_ID;

      FETCH NEXT FROM c INTO @FIELD_TO_UPDATE, @MY_TABLE_ID;
    END

    CLOSE c;
    DEALLOCATE c;

也就是说，如果您认为这个解决方案比连接的解决方案更快，那么我在佛罗里达州有一些沼泽地要卖给您.该物业也有多座桥梁.我什至不想费心展示这个的执行计划.

That said, if you think even for a second that this solution is going to be faster than the one with joins, I have some swampland in Florida to sell you. There are multiple bridges on the property, too. I'm not even going to bother showing the execution plans for this one.

我们还比较一下 INSTEAD OF INSERT 触发器中发生的情况.这是一个示例，可能与您的示例类似:

Let's also compare what happens in an INSTEAD OF INSERT trigger. Here is an example, probably similar to what you had:

CREATE TRIGGER myschema.ins_my_table
ON myschema.MY_TABLE
INSTEAD OF INSERT
AS
  INSERT myschema.MY_TABLE(FIELD_TO_UPDATE, CREATE_TS)
    SELECT FIELD_TO_UPDATE, CURRENT_TIMESTAMP FROM inserted;
GO

这也会产生一个看起来像是执行了两个查询的计划:

This, too, will produce a plan that looks like two queries were executed:

需要注意的是，INSTEAD OF 触发器会取消原始更新，您有责任发布自己的更新(即使计划仍显示两个查询).

It is important to note that an INSTEAD OF trigger cancels the original update, and you are responsible for issuing your own (even though the plan still shows two queries).

最后一个选择是使用 AFTER 触发器而不是 INSTEAD OF 触发器.这将允许您在没有 JOIN 的情况下更新时间戳，因为 FIELD_TO_UPDATE 已经更新.但在这种情况下，您确实会看到两个查询，并且会真正执行两个查询(在计划中不会只是这样).

One final option would be to use an AFTER trigger instead of an INSTEAD OF trigger. This will allow you to update the timestamp without the JOIN, because the FIELD_TO_UPDATE has already been updated. But in this case you really will see two queries, and two queries will really be executed (it won't just look that way in the plans).

一些一般性评论

由于我要提高性能，我不想在用于触发器的代码中使用任何内部联接.

Since i'm going for performance increase I do not want to use any inner joins in the code used for the trigger.

这并没有多大意义；为什么你认为连接对性能不利?听起来您观看了太多 NoSQL 视频.请不要因为您听说它不好或因为您曾经有过一次缓慢加入而丢弃技术.创建有意义的查询，在性能不佳时进行优化，在无法优化时寻求帮助.在几乎所有情况下(当然也有例外)，问题在于索引或统计数据，而不是您使用了 JOIN 关键字这一事实.这并不意味着您应该不惜一切代价避免所有查询中的所有连接.

This doesn't really make much sense; why do you think joins are bad for performance? Sounds like you've watched too many NoSQL videos. Please don't discard technology because you've heard it was bad or because you had a slow join once. Create the query that makes sense, optimize when it doesn't perform well, and come for help when you can't optimize. In almost all cases (there are exceptions, of course), the problem is indexing or statistics and not the fact that you used a JOIN keyword. That doesn't mean you should avoid all joins in all queries at all costs.

这篇关于SQL Server BEFORE UPDATE 触发器，它在执行 UPDATE 之前在字段上添加时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！