That is brilliant. I was running this on Oracle 9.2.0.5 on HP UNIX 11i.
It seems to me
some kind of bug in Oracle if it fixed in Oracle 10G.
Prem
Jonathan Lewis wrote:
> <dbaplusplus_at_hotmail.com> wrote in message
> news:1127863044.655753.185680_at_g47g2000cwa.googlegroups.com...
> >
> > I have a table test. I have set up a trigger (before insert)
> >
> > CREATE OR REPLACE TRIGGER tr_test_row
> > BEFORE INSERT ON test
> > FOR EACH ROW
> >
> >
> > There is a primary key on table test on name column. There is already a
> > row in test table.
> >
> > When I insert a row with same name as an existing row, Oracle does not
> > fire the trigger (I have put debugging statements in the trigger)
> > instead it immediately sends me a message that unique constraint
> > violated. Why? I was expecting that the uniqueness check will be done
> > after trigger is fired (it is a before insert trigger). I have error
> > catching logic in my trigger and want to capture all Oracle errors
> > including unique constraint violation in the trigger. Is there a way of
> > doing that?
> >
> > Any pointers will be appreciated. Thanks a lot.
> >
>
>
> I think you'll find that the trigger is firing -
> it may be what you're doing inside the
> trigger that is fooling you:
>
>
> create table t1 (id number, v1 varchar2(10));
> alter table t1 add constraint t1_pk primary key (id);
>
> create or replace trigger t1_bri
> before insert on t1
> for each row
> begin
> dbms_output.put_line(:new.id);
> end;
> /
>
> -----------------------------------
>
> set serveroutput on
> insert into t1 values(1,'x');
> 1
>
> 1 row created.
>
> SQL> insert into t1 values(1,'x');
> insert into t1 values(1,'x')
> *
> ERROR at line 1:
> ORA-00001: unique constraint (TEST_USER.T1_PK) violated
>
> SQL> insert into t1 values(2,'x');
> 1
> 2
>
> 1 row created.
>
> ---------------------------------------
>
> Note that the call from sql*plus to read
> the dbms_output buffer does not appear
> if an for the error has occurred. (I think
> this changes in 10g). But it is there waiting
> for the next successful event.
>
> Similarly, if you are inserting errors to a table,
> then an error will cause the triggered insert
> to rollback unless you are using an autonomous
> transaction (and they should generally be avoided
> for performance reasons as well as technical
> reasons).
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> Cost Based Oracle - Volume 1: Fundamentals
> On-shelf date: Nov 2005
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/appearances.html
> Public Appearances - schedule updated 4th Sept 2005
Received on Wed Sep 28 2005 - 17:33:11 CDT
