考虑一下这个触发器:
ALTER TRIGGER myTrigger
ON someTable
AFTER INSERT
AS BEGIN
DELETE FROM someTable
WHERE ISNUMERIC(someField) = 1
END我有一个表someTable,并且我试图防止人们插入错误的记录。就本问题而言,坏记录的字段"someField“全是数字。
当然,正确的方法不是使用触发器,但我不能控制源代码……只有SQL数据库。因此,我不能真正阻止插入坏行,但我可以立即删除它,这对我的需求来说已经足够好了。
触发器起作用了,但有一个问题...当它触发时,它似乎永远不会删除刚刚插入的错误记录...它会删除所有旧的坏记录,但不会删除刚刚插入的坏记录。因此,经常会有一条坏记录漂浮在周围,直到其他人出现并执行另一次插入时才会被删除。
这是我对触发器的理解上的问题吗?触发器正在运行时,新插入的行是否尚未提交?
发布于 2009-01-01 18:52:50
触发器不能修改更改的数据(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.发布于 2009-01-01 20:04:32
你可以颠倒逻辑。不是在插入无效行后将其删除,而是仅在验证该行有效时才编写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上运行了它,它工作正常。请注意,如果将insert插入为其定义触发器的同一个表中,则INSTEAD OF触发器不会导致递归。
发布于 2009-01-22 14:20:52
下面是我修改过的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中,您可以在以后处理它们。让您的rejects表对所有内容使用nvarchar字段-而不是it、tinyint等-这一点很重要,因为如果它们被拒绝,那是因为数据不是您期望的数据。
这也解决了多记录插入问题,这将导致Bill的触发器失败。如果同时插入10条记录(比如执行select- insert -into操作),并且只有一条记录是假的,那么Bill的触发器会将所有记录都标记为错误。它可以处理任意数量的好记录和坏记录。
我在一个数据仓库项目中使用了这个技巧,在这个项目中,插入应用程序不知道业务逻辑是否好,而是在触发器中执行业务逻辑。对于性能来说确实很糟糕,但是如果你不能让插入失败,它还是可以工作的。
https://stackoverflow.com/questions/405288
复制相似问题