How to prevent an Insert Trigger from being fired when no row is inserted ?

Question

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?

Answer 1

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

1. A BEFORE trigger to set a package boolean variable to FALSE
2. A FOR EACH ROW trigger to set the variable to TRUE when a row is inserted
3. The AFTER trigger you have, where you can now check the value of the variable before carrying out its action.

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

Answer 2

This is the normal behaviour, Oracle is expecting to insert row so it fires both BEFORE INSERT and AFTER INSERT triggers, even if no row is inserted. One of the purpose of the AFTER triggers is to do some action after a statement succeeds. This statement succeeded :)

You could count the number of rows affected with a package variable:

• set the mypackage.table1_nb_ins variable to 0 in a BEFORE INSERT trigger
• increment the counter in a AFTER INESRT FOR EACH ROW trigger

and then perform your AFTER INSERT logic if your counter is greater than 0