Instead-of trigger spurious message
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 30 Jan 2010 10:43:43 -0800 (PST)
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
Other related posts:
