只更新插入行的触发器 [英] Trigger that updates just the inserted row

问题描述

我正在尝试使用 TSQL(或 SQL Server 2008)创建一个简单的触发器.问题是:我当前的触发器正在更新整个表.这有一段时间很好，但现在表有超过 20k 行.所以我想要一个只更新正在插入的行的触发器.

I'm trying to create a simple trigger using TSQL (or SQL Server 2008). The problem is: my current trigger is updating the entire table. This was fine for a while, but now the table has more than 20k rows. So I want a trigger that only updates the rows that are being inserted.

这是我目前的简单触发器:

Here's my current simple trigger:

CREATE TRIGGER trig_MyPplUpdate
ON [Persons]
FOR INSERT
AS
Begin
 Update Persons
    set MyFile = NULL
    where Len(MyFile) < 60
End

我想我必须使用插入"表或按主键排序的 row_number 函数.有什么想法吗?

I think I'll have to use either the "inserted" table or the row_number function ordered by the primary key. Any ideas?

推荐答案

如果有必要在这里使用触发器，我会使用 INSTEAD OF 触发器来调整预插入和避免需要 JOIN 回到基表并在之后更新它们.

If it is necessary to use a trigger here at all I would use an INSTEAD OF trigger to adjust the values pre-insert and avoid the need to JOIN back onto the base table and Update them afterwards.

CREATE TRIGGER trig_MyPplUpdate
ON [Persons]
INSTEAD OF INSERT
AS
  BEGIN
      INSERT INTO Persons
      SELECT foo,
             bar,
             CASE
               WHEN Len(MyFile) >= 60 THEN MyFile
             END
      FROM   Inserted
  END  

这篇关于只更新插入行的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！