The text, "One row created" is produced from sql*plus. To turn that off, I believe you use the sql*plus command, "SET FEEDBACK OFF". R, gus On Sat, Jan 30, 2010 at 1:43 PM, Yong Huang <yong321@xxxxxxxxx> wrote: > Oracle 10.2.0.4. 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); > Good! > > 1 row created. > > SQL> select * from t; > > X > ------------ > 9 > > 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: > > INSERT INTO T VALUES (:B1 ) > ... > Bind#0 > ... > value=9 > > 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 > > > > -- > //www.freelists.org/webpage/oracle-l > > >