Re: Statement Level Trigger Does Not Fire - 11.2.0.1
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: mdinh@xxxxxxxxx
- Date: Fri, 24 Sep 2010 21:09:03 +0200
Michael,
The absence of any message/warning or whatever is disturbing, but
otherwise I'm not shocked by the trigger not firing. It's a BEFORE
statement insert. BEFORE you have inserted the value, the row has no
physical existence, therefore no rowid, and therefore I don't see how
you could try to insert the pair (key, rowid) in the index and raise the
exception. Or am I erring?
Check what DBA_OBJECTS says about the state of the trigger, but for me
there is a logical error in the design of the trigger.
HTH,
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 09/24/2010 07:48 PM, 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;
>
>
>
>
>
> SQL> select * from v$version;
>
>
>
> BANNER
>
> --------------------------------------------------------------------------------
>
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
> Production
>
> PL/SQL Release 11.2.0.1.0 - Production
>
> CORE 11.2.0.1.0 Production
>
> TNS for Solaris: Version 11.2.0.1.0 - Production
>
> NLSRTL Version 11.2.0.1.0 - Production
>
>
>
> SQL> 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);
>
>
>
> Table dropped.
>
>
>
> SQL>
>
> Table dropped.
>
>
>
> SQL>
>
> Table created.
>
>
>
> SQL>
>
> Table created.
>
>
>
> SQL>
>
> Index created.
>
>
>
> SQL> 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;
>
> /
>
> 2 3 4 5 6 7 8 9 10
>
> Trigger created.
>
>
>
> SQL> insert into t values (1);
>
> commit;
>
> select * from t;
>
> select * from taud;
>
>
>
> 1 row created.
>
>
>
> SQL>
>
> Commit complete.
>
>
>
> SQL>
>
> ID
>
> ----------
>
> 1
>
>
>
> SQL>
>
> no rows selected
>
>
>
> SQL>
>
>
>
>
>
> NOTICE OF CONFIDENTIALITY - This material is intended for the use of
> the individual or entity to which it is addressed, and may contain
> information that is privileged, confidential and exempt from
> disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL
> MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS
> MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU
> AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
> EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO
> PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If
> the reader of this email (and attachments) is not the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited.
> Please notify the sender of the error and delete the e-mail you
> received. Thank you.
>
Other related posts:
