Take the 2-minute tour ×

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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?

share|improve this answer
+1: beat me to it :) –  Vincent Malgrat Jun 25 '10 at 13:28
Actually, i just added FOR EACH ROW and it solves my problem. i just need to update a date in another table. Even if the trigger is fired for every line inserted, in ,y case it doesnt matter since there are very few rows inserted each time. But thanks for the answer. I ll try if i have performance issues. –  guigui42 Jun 25 '10 at 13:30

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

share|improve this answer
+1 Great minds... ;-) –  Tony Andrews Jun 25 '10 at 13:30

Your Answer


