Re: Instead-of trigger spurious message

     Oracle says "1 row created" when it executes an insert ... values ... statement and that it doesn't return an error. You did nothing in your trigger to make something fail ...

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        raise_application_error(-20000,  'Too big!');
  7      else
  8        insert into t values (:new.x);
  9        dbms_output.put_line('Good!');
 10      end if;

Trigger created.

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

            *
ERROR at line 1:

ORA-20000: Too big!
ORA-06512: at "TEST.T_V", line 3
ORA-04088: error during execution of trigger 'TEST.T_V'

SQL> select * from t;

no rows selected

SQL> insert into v values (9);
Good!

1 row created.

SQL> select * from t;

     X

     9

SQL> Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

Yong Huang 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