Re: Instead-of trigger spurious message

From: Gus Spier <gus.spier_at_gmail.com>
Date: Sat, 30 Jan 2010 18:51:46 -0500
Message-ID: <6d3967611001301551j29a7a1d1h4680b58197f90620_at_mail.gmail.com>

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_at_yahoo.com> 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jan 30 2010 - 17:51:46 CST