I wasn't clear, it should say "not visibl...

2010-01-06T13:36:43.800+11:00

I wasn't clear, it should say "not visible to the trigger".
They are visible to the main transaction. However the SERVERERROR trigger runs as a separate transaction. You see it output the "Trigger ran" line, but the loop doesn't see any rows in the table so you don't see a "1:test" or "10:test 10" output line as those rows belong to a separate, uncommitted transaction.

Hi Gary, You said " but the two successfu...

2010-01-06T03:37:46.900+11:00

Hi Gary,

You said " but the two successful inserts are not visible (because the trigger is an autonomous transaction)."

Is this a typo or am I missing something ?

I got on 11GR1 and 10GR2

SQL> create table test_se_auto_tbl
2 (id number(2) primary key, val varchar2(20));

Table created.

SQL>
SQL> create or replace trigger test_se_auto_trg
2 after servererror on schema
3 begin
4 dbms_output.put_line('Trigger ran');
5 for c_rec in (select id, val from test_se_auto_tbl) loop
6 dbms_output.put_line(c_rec.id||':'||c_rec.val);
7 end loop;
8 end;
9 /

Trigger created.

SQL> insert into test_se_auto_tbl values (1,'test ');

1 row created.

SQL> insert into test_se_auto_tbl values (10,'test 10');

1 row created.

SQL> insert into test_se_auto_tbl values (100,'test 100');
Trigger ran
insert into test_se_auto_tbl values (100,'test 100')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL>
SQL> select id, val from test_se_auto_tbl;

ID VAL
---------- --------------------
1 test
10 test 10

Two rows are visible ?

Comments on Sydney Oracle Lab: Autonomous SERVERERROR trigger

I wasn't clear, it should say "not visibl...

Hi Gary, You said " but the two successfu...