SQL Server“ AFTER INSERT”触发器没有看到刚插入的行 [英] SQL Server "AFTER INSERT" trigger doesn't see the just-inserted row

问题描述

考虑此触发器：

ALTER TRIGGER myTrigger 
   ON someTable 
   AFTER INSERT
AS BEGIN
  DELETE FROM someTable
         WHERE ISNUMERIC(someField) = 1
END

我有一个表someTable，并且我试图防止人们插入不良记录。出于此问题的目的，一个错误的记录具有一个全为数字的字段 someField。

I've got a table, someTable, and I'm trying to prevent people from inserting bad records. For the purpose of this question, a bad record has a field "someField" that is all numeric.

当然，正确的方法不是使用触发器，但我不控制源代码...仅控制SQL数据库。因此，我无法真正阻止插入不良行，但可以立即将其删除，这足以满足我的需求。

Of course, the right way to do this is NOT with a trigger, but I don't control the source code... just the SQL database. So I can't really prevent the insertion of the bad row, but I can delete it right away, which is good enough for my needs.

触发器的工作原理如下：一个问题...启动时，它似乎从未删除刚刚插入的不良记录...它删除了所有旧的不良记录，但没有删除刚刚插入的不良记录。因此，经常有一条糟糕的记录在周围徘徊，直到其他人出现并再次插入之前才被删除。

The trigger works, with one problem... when it fires, it never seems to delete the just-inserted bad record... it deletes any OLD bad records, but it doesn't delete the just-inserted bad record. So there's often one bad record floating around that isn't deleted until somebody else comes along and does another INSERT.

在我对触发器的理解上，这是一个问题吗？触发器运行时是否尚未提交新插入的行？

Is this a problem in my understanding of triggers? Are newly-inserted rows not yet committed while the trigger is running?

推荐答案

触发器无法修改更改的数据（已插入或已删除），否则当更改再次调用触发器时，您将获得无限递归。一种选择是触发器回滚事务。

Triggers cannot modify the changed data (Inserted or Deleted) otherwise you could get infinite recursion as the changes invoked the trigger again. One option would be for the trigger to roll back the transaction.

编辑：原因是SQL的标准是插入的触发器无法修改已删除的行。其根本原因是修改可能导致无限递归。在一般情况下，此评估可能会在相互递归级联中包含多个触发器。让系统智能地决定是否允许此类更新在计算上是棘手的，本质上是暂停问题的替代方案。

The reason for this is that the standard for SQL is that inserted and deleted rows cannot be modified by the trigger. The underlying reason for is that the modifications could cause infinite recursion. In the general case, this evaluation could involve multiple triggers in a mutually recursive cascade. Having a system intelligently decide whether to allow such updates is computationally intractable, essentially a variation on the halting problem.

尽管可以回滚事务，但可接受的解决方案是不允许触发器更改变化的数据。

The accepted solution to this is not to permit the trigger to alter the changing data, although it can roll back the transaction.

create table Foo (
       FooID int
      ,SomeField varchar (10)
)
go

create trigger FooInsert
    on Foo after insert as
    begin
        delete inserted
         where isnumeric (SomeField) = 1
    end
go


Msg 286, Level 16, State 1, Procedure FooInsert, Line 5
The logical tables INSERTED and DELETED cannot be updated.

像这样的东西会回滚交易。

Something like this will roll back the transaction.

create table Foo (
       FooID int
      ,SomeField varchar (10)
)
go

create trigger FooInsert
    on Foo for insert as
    if exists (
       select 1
         from inserted 
        where isnumeric (SomeField) = 1) begin
              rollback transaction
    end
go

insert Foo values (1, '1')

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

这篇关于SQL Server“ AFTER INSERT”触发器没有看到刚插入的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！