exception on unique index not being trapped in 9.2

[SirCharles]

I thought I was able to trap exceptions for unique indexes on oracle 8. Seems that with Oracle 9.2, this may not
be the same?

I'd like to update a unique field, unique_hr_name, based with value of other field - hr_name. If hr_name is not unique, tack unique value onto unique_hr_name.

Unique index is on unique_hr_name but not hr_name.



CREATE OR REPLACE TRIGGER my_table_unique_hr_name_biu
before insert or update
on my_table
for each row
declare
cnt_hr_name number := 0;
place_holder varchar2(50):= 0;

begin
--select hr_name into place_holder
--from my_table
--where status != 'T'
--and hr_name like :new.hr_name;

-- if found then do something
dbms_output.put_line('my_table_unique_hr_name_biu 1484');
begin
:new.unique_hr_name := :new.hr_name;
exception
when dup_val_on_index then
dbms_output.put_line('sqlerr 1489:'||SQLERRM(SQLCODE));
end;

exception
when dup_val_on_index then
dbms_output.put_line('sqlerr 1494:'||SQLERRM(SQLCODE));
when others then


-- if not found then do something else
dbms_output.put_line('my_table_unique_hr_name_biu sqlerror 1499:'||SQLERRM(SQLCODE));
-- update my_table set unique_hr_name = :new.hr_name||' zzz'||:new.user_id where user_id = :new.user_id;
end;
/