Instead-of trigger spurious message

From: Yong Huang <yong321_at_yahoo.com>
Date: Sat, 30 Jan 2010 10:43:43 -0800 (PST)
Message-ID: <273449.95344.qm_at_web80601.mail.mud.yahoo.com>

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

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