SQL Server“插入后"触发器没有看到刚刚插入的行

2022-01-01 00:00:00 sql-server-2005 sql-server triggers

考虑这个触发器:

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.

触发器工作，有一个问题...当它触发时，它似乎永远不会删除刚刚插入的坏记录...它删除任何旧的坏记录，但它不会删除刚刚插入的坏记录.因此，经常有一个错误的记录四处飘散，直到其他人出现并执行另一个 INSERT 时才会删除.

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?

推荐答案

触发器不能修改改变的数据(Inserted 或 Deleted)，否则你会得到无限递归，因为更改再次调用触发器.一种选择是触发器回滚事务.

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.

相关文章