SQL Server“AFTER INSERT”触发器看不到刚刚插入的行

触发器无法修改更改的数据( Inserted 或 Deleted )否则,当更改再次调用触发器时,您可能会得到无限递归。一种选择是触发器回滚交易。

编辑: 原因是SQL的标准是插入和删除的行不能被触发器修改。根本原因是这些修改可能会导致无限递归。在一般情况下,这种评估可能涉及相互递归级联中的多个触发器。让系统智能地决定是否允许此类更新在计算上是棘手的,本质上是对 halting problem.

公认的解决方案是不允许触发器更改更改更改的数据,尽管它可以回滚事务。

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.

这样的操作将回滚交易。

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.

你可以颠倒逻辑。不要在插入无效行后将其删除,而是写入 INSTEAD OF 触发器插入 仅 如果您验证该行是否有效。

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

如果你的应用程序不想处理错误(正如Joel在他的应用程序中所说),那么就不要 RAISERROR 只需默默地扣动扳机 不 插入无效的内容。

我在SQL Server Express 2005上运行了这个程序,它工作正常。注意 而不是 触发器 不要 如果您插入到定义触发器的同一表中,则会导致递归。

这是我对比尔代码的修改版本:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

这使得插入始终成功,任何虚假记录都会被扔进sometableRejects中,以便以后处理。重要的是让你的拒绝表对所有内容都使用nvarchar字段,而不是int、tinyints等,因为如果它们被拒绝,那是因为数据不是你期望的那样。

这也解决了多记录插入问题,这将导致Bill的触发器失败。如果你同时插入十条记录(就像你选择插入一样),其中只有一条是伪造的,比尔的触发器会将所有记录标记为错误。这可以处理任何数量的好记录和坏记录。

我在一个数据仓库项目中使用了这个技巧,在这个项目中,插入应用程序不知道业务逻辑是否良好,我们在触发器中执行了业务逻辑。对性能来说真的很糟糕,但如果你不能让插件失败,它确实有效。

我认为你可以使用CHECK约束——这正是它被发明的目的。

ALTER TABLE someTable 
ADD CONSTRAINT someField_check CHECK (ISNUMERIC(someField) = 1) ;

我之前的回答(也可能有点过头了):

我认为正确的方法是使用INSTEAD OF触发器来防止插入错误的数据(而不是事后删除)

更新:触发器中的DELETE在MSSql 7和MSSql 2008上都有效。

我不是关系专家,也不是SQL标准专家。然而,与公认的答案相反,MSSQL在这两个方面都做得很好 ecursive and nested trigger evaluation 。我不知道其他RDBMS。

相关选项包括 'recursive triggers' and 'nested triggers' 嵌套触发器限制为32级,默认为1级。递归触发器默认情况下是关闭的,也没有限制的说法——但坦率地说,我从未打开过它们,所以我不知道不可避免的堆栈溢出会发生什么。我怀疑MSSQL只会杀死你的蜘蛛(或者有一个递归限制)。

当然,这只是表明公认的答案是错误的 原因 ,并不是说这是不正确的。然而,在INSTEAD OF触发器之前,我记得在INSERT触发器上写过,它会愉快地更新刚刚插入的行。这一切都很好,正如预期的那样。

快速测试删除刚刚插入的行也有效:

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

你这里还有别的事。

从 CREATE TRIGGER 文档:

删除 和 插入 是逻辑(概念)表。他们是 结构上与桌子相似 该触发器是定义的, 用户操作所在的表 尝试并保持旧值或 可能存在的行的新值 由用户动作改变。给 例如,检索 已删除表,请使用: SELECT * FROM deleted

因此,这至少为您提供了一种查看新数据的方式。

我在文档中看不到任何指定在查询普通表时看不到插入的数据的内容。..

我找到了这个参考:

create trigger myTrigger
on SomeTable
for insert 
as 
if (select count(*) 
    from SomeTable, inserted 
    where IsNumeric(SomeField) = 1) <> 0
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "You can't do that!"  
  end  
/* Otherwise, allow it. */
else
  print "Added successfully."

我还没有测试它,但从逻辑上讲,它看起来应该符合你的要求。..与其删除插入的数据,不如完全阻止插入,从而不需要您撤消插入。它应该表现得更好,因此最终应该更容易处理更高的负载。

编辑:当然有 是 如果插入发生在原本有效的事务中,wole事务可能会被回滚,因此您需要考虑这种情况,并确定插入无效数据行是否构成完全无效的事务。..

有没有可能INSERT是有效的,但之后进行的单独UPDATE是无效的,但不会触发触发器?

上面概述的技术很好地描述了你的选择。但是用户看到了什么?我无法想象,你和负责软件的人之间的这种基本冲突怎么会不会最终导致与用户的混淆和对抗。

我会尽我所能找到摆脱僵局的其他方法,因为其他人很容易看到你所做的任何改变都会使问题升级。

编辑:

我会给我的第一个“取消删除”打分,并承认在这个问题第一次出现时发布了上述内容。当我看到它来自乔尔·斯波尔斯基时,我当然吓了一跳。但看起来它降落在附近的某个地方。不需要投票,但我会把它记录在案。

IME触发器很少是业务规则领域之外细粒度完整性约束之外的正确答案。

MS-SQL有一个防止递归触发器触发的设置。这是通过sp_configure存储过程配置的,您可以在其中打开或关闭递归或嵌套触发器。

在这种情况下,如果您关闭递归触发器,通过主键从插入的表链接记录,并对记录进行更改,这是可能的。

在问题的具体情况下,这并不是一个问题,因为结果是删除记录,这不会重新触发这个特定的触发器,但一般来说,这可能是一种有效的方法。我们以这种方式实现了乐观并发。

可以以这种方式使用的触发器代码是:

ALTER TRIGGER myTrigger
    ON someTable
    AFTER INSERT
AS BEGIN
DELETE FROM someTable
    INNER JOIN inserted on inserted.primarykey = someTable.primarykey
    WHERE ISNUMERIC(inserted.someField) = 1
END

你的“触发器”正在做一些“触发器”不应该做的事情。您可以简单地运行Sql Server代理

DELETE FROM someTable
WHERE ISNUMERIC(someField) = 1

每1秒左右。当你这样做的时候,写一个漂亮的小SP来阻止编程人员在你的表中插入错误怎么样。SP的一个优点是参数是类型安全的。

我在插入语句时偶然发现了这个问题,想了解事件顺序的细节;触发。我最终编写了一些简短的测试来确认SQL 2016(EXPRESS)的行为,并认为分享它是合适的,因为它可能会帮助其他人搜索类似的信息。

根据我的测试,可以从“插入”表中选择数据,并使用它来更新插入的数据本身。而且,我感兴趣的是,在触发器完成之前,插入的数据对其他查询是不可见的,此时最终结果是可见的(至少我可以测试到最好的结果)。我没有测试递归触发器等。(我希望嵌套触发器能够完全看到表中插入的数据,但这只是猜测)。

例如,假设我们的表“table”有一个整数字段“field”和主键字段“pk”,并且插入触发器中有以下代码:

select @value=field,@pk=pk from inserted
update table set field=@value+1 where pk=@pk
waitfor delay '00:00:15'

我们为“field”插入一个值为1的行,然后该行将以值2结束。此外,如果我在Contoso中打开另一个窗口并尝试: 从pk=@pk的表中选择*

其中@pk是我最初插入的主键,查询将为空,直到15秒到期,然后将显示更新的值(字段=2)。

我对触发器执行时其他查询可见的数据感兴趣(显然没有新数据)。我也测试了添加删除:

select @value=field,@pk=pk from inserted
update table set field=@value+1 where pk=@pk
delete from table where pk=@pk
waitfor delay '00:00:15'

同样,插入需要15秒才能执行。在不同会话中执行的查询在执行插入+触发器期间或之后没有显示新数据(尽管我预计即使没有插入数据,任何标识也会增加)。