Re: Before Insert Trigger

<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