Instead-of trigger spurious message

From: Yong Huang <>
Date: Sat, 30 Jan 2010 10:43:43 -0800 (PST)
Message-ID: <>

Oracle Insert into a view with an instead-of trigger shows "rows created" but that's not true.

SQL> create table t (x int);

Table created.

SQL> create view v as select * from t;

View created.

SQL> create or replace trigger t_v
  2 instead of insert on v
  3 for each row
  4 begin
  5 if :new.x > 10 then
  6 dbms_output.put_line('Too big!');   7 else

  8      insert into t values (:new.x);
  9      dbms_output.put_line('Good!');

 10 end if;
 11 end;
 12 /

Trigger created.

SQL> set serverout on
SQL> insert into v values (11);
Too big!

1 row created. <-- How can I suppress this?

SQL> select * from t;

no rows selected

SQL> insert into v values (9);

1 row created.

SQL> select * from t;



SQL trace indicates that the first insert (of value 11) does not have a recursive SQL to insert into table T. But the second insert (of value 9) does:




The behavior is correct. But I'd like to not see the spurious message "1 row created" when that row is indeed not created. I guess the message is about the user-level SQL (insert into v) and indeed it has no error (SQL trace has "EXEC ... r=1"). Whether the recursive SQL (INSERT INTO T) succeeds and how many rows are inserted is not reflected in this feedback message.

Yong Huang       

Received on Sat Jan 30 2010 - 12:43:43 CST

Original text of this message