没有插入行时如何防止触发插入触发器？ [英] How to prevent an Insert Trigger from being fired when no row is inserted?

问题描述

我有一个TABLE1。在此表上，我创建了一个触发器：在插入，更新或删除之后

I have a TABLE1. On this table I created a trigger : AFTER INSERT OR UPDATE OR DELETE

现在，如果我执行不插入任何内容的插入操作，则仍然会触发该触发器：

Now, if I do an insert which doesn't insert anything, the trigger will still be fired :

insert into TABLE1 select * from TABLE1 where 1=0;

此查询将不插入行，但触发器仍会触发。

This query will insert NO ROWS but yet the trigger is still fired.

有办法避免这种情况吗？这是正常行为吗？

Is there a way to avoid this ? Is this normal behavior?

推荐答案

是的，这是正常行为。可以避免，尽管这样做需要3个触发器：

Yes, it is normal behaviour. It can be avoided, though doing so requires having 3 triggers:

1. 将包布尔变量设置为FALSE的BEFORE触发器


2. 一个FOR EACH ROW触发器，用于在插入行时将变量设置为TRUE


3. 具有AFTER触发器，您现在可以在其中检查变量的值

听起来像是过分杀伤力？也许是：用触发器来尝试实现什么？

Sounds like overkill? Maybe it is: what are to trying to achieve with your trigger?

这篇关于没有插入行时如何防止触发插入触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！