trigger to set unique field value based on other field's value.

[SirCharles]

I'd like to update a unique field, field_unique, based on incoming value of another field, field_not_unique.

Ideally a unique index on the field_unique field and a trigger to trap an on_dup_val:
create or replace trigger table_name_field_biu
before insert or update
on table_name
for each row
declare
begin
:new.field_unique := :new.field_not_unique;
exception
when others then
:new.field_unique := :new.field_not_unique||field_pk;
end;
/


Problem is that unique index on field_unique throws exception that does not appear to be trapped in this trigger.

Any ideas?

 

[sem]

Obviously, the only error that may be raised by this new.field_unique := :new.field_not_unique assignment and trapped by EXCEPTIONS block is VALUE_ERROR, as no SQL statemnt is processed within BEGIN..END.

I'd suggest you to handle this in your calling application.

Regards, Dima

http://seminihin.narod.ru

 

[SirCharles]

Hi Dima,
Thanks for your reply on this post.

There is no calling application that I have control over. This is a shared DB. Sometimes updated by ODBC from MS Access. So, I'm using trigger to set value.

 

[sem]

First of all you should get rid of declarative constraint, because it's checked before firing any triggers.

Then, if this suits you, you may act like with mutating error: store changes in some temporary location (collection package variable or tempjrary table) and apply them with verification in after insert statement level trigger. Although this solution seems to be ugly I can not know a better one. Except forcing client applications to follow some rules (either not provide ID at all or to be ready to process DUP_VAL_ON_INDEX).

Regards, Dima

http://seminihin.narod.ru

 

[SirCharles]

How to get rid of declarative constraint?

And what is a 'collection package variable' and how is
it used?

 

[SirCharles]

How about if instead of the following in the exeption block
or after the begin
:new.field_unique := :new.field_not_unique||field_pk;

we call a function (get_unique_value) which uses pragma autonomous_transaction
:new.field_unique := get_unique_valuenew.field_unique);