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

考虑这个触发器:

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

我有一张桌子,有桌子,我试图阻止人们插入不良记录.出于这个问题的目的,坏记录有一个字段"someField",它都是数字.

当然,正确的方法不是触发器,但我不控制源代码......只是SQL数据库.所以我无法阻止插入坏行,但我可以立即将其删除,这对我的需求来说已经足够了.

触发器有效,有一个问题......当它触发时,似乎永远不会删除刚刚插入的坏记录......它会删除任何旧的坏记录,但它不会删除刚刚插入的坏记录.所以通常会有一个糟糕的记录在没有删除,直到其他人出现并执行另一次INSERT.

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

1> ConcernedOfT..：

---

触发器无法修改已更改的数据(Inserted或Deleted),否则您可能会获得无限递归,因为更改会再次调用触发器.一种选择是触发器回滚事务.

编辑:原因是SQL的标准是插入和删除的行不能被触发器修改.根本原因是修改可能导致无限递归.在一般情况下,此评估可能涉及相互递归级联中的多个触发器.让系统智能地决定是否允许这样的更新在计算上是难以处理的,本质上是停止问题的变化.

可接受的解决方案是不允许触发器改变变化的数据,尽管它可以回滚事务.

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.

2> Bill Karwin..：

---

你可以扭转逻辑.插入后,只有在验证行有效时才写入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上运行它,它的工作原理.请注意,INSTEAD OF触发不,如果你插入扳机被定义为其同桌引起递归.

---

比尔你的解决方案是一个不好的实践触发器.它只有在插入一条记录时才有效.必须设计所有触发器以处理多个记录插入.

3> Brent Ozar..：

---

这是Bill的代码的修改版本:

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字段来处理所有事情 - 而不是整数,细小等等 - 因为如果它们被拒绝,那是因为数据不是你想象的那样.

这也解决了多记录插入问题,这将导致Bill的触发器失败.如果你同时插入十个记录(就像你选择插入一样)并且其中一个是假的,那么Bill的触发器会将所有记录标记为坏.这可以处理任意数量的好记录和坏记录.

我在数据仓库项目中使用了这个技巧,其中插入应用程序不知道业务逻辑是否有用,而我们在触发器中执行了业务逻辑.真的很讨厌性能,但如果你不能让插入失败,它确实有效.

4> Dmitry Khala..：

---

我认为你可以使用CHECK约束 - 它正是它的发明.

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

我以前的回答(也可能有点矫枉过正):

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

5> Mark Bracket..：

---

更新:从触发器删除适用于MSSql 7和MSSql 2008.

我不是关系大师,也不是SQL标准.然而 - 与已接受的答案相反 - MSSQL对递归和嵌套触发器评估都做得很好.我不知道其他RDBMS.

相关选项是"递归触发器"和"嵌套触发器".嵌套触发器限制为32级,默认为1.默认情况下,递归触发器处于关闭状态,并且没有谈论限制 - 但坦率地说,我从未打开它们,所以我不知道不可避免的会发生什么堆栈溢出.我怀疑MSSQL会杀死你的spid(或者有一个递归限制).

当然,这只是表明接受的答案有错误的原因,而不是它是不正确的.但是,在INSTEAD OF触发器之前,我记得编写ON 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

你还有别的东西在这里.