Re: Statement Level Trigger Does Not Fire - 11.2.0.1

From: De DBA <dedba_at_tpg.com.au>
Date: Mon, 27 Sep 2010 10:00:30 +1000
Message-ID: <4C9FDE9E.9090604_at_tpg.com.au>

I tried the scenario on my 11.2.0.1 test database (64-bit linux) as an ordinary user and got the same result - trigger did not insert anything into table TAUD. But then I ran the plustrace.sql script (as sys) and granted PLUSTRACE to public. I quit and restarted sql*plus and enabled autotrace. This should not make any difference at all of course, but when I did the insert into t again, the trigger *did* fire and a row appeared in TAUD. It continues to fire, even when I set autotrace off and revoke PLUSTRACE... perhaps a furphy, but intreguing nevertheless.

Again, this goes against all logic of course..

Cheers,
Tony

Michael Dinh wrote:
>
> Has anyone experience this?
>
>
>
> Thanks for any assistance.
>
>
>
> Test Case:
>
>
>
> drop table t purge;
>
> drop table taud purge;
>
> create table t(id int);
>
> create table taud(tn varchar2(30));
>
> create unique index tx on taud(tn);
>
>
>
> create or replace trigger biud$t
>
> BEFORE UPDATE OR INSERT OR DELETE
>
> on t
>
> begin
>
> insert into taud values ('t');
>
> exception
>
> when dup_val_on_index then -- this table name was already inserted, ok
>
> null;
>
> end;
>
> /
>
>
>
> insert into t values (1);
>
> commit;
>
> select * from t;
>
> select * from taud;
>
>
>
>
>

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

Received on Sun Sep 26 2010 - 19:00:30 CDT