Re: Mutating triggers

Chris Halioris <halioris_chris_nonlilly_at_lilly.com> wrote:
>I have a table that has a conditional unique constraint on it.
>Assume I have two columns: parm_code and code_value.
>If parm_code = 'A' then the code_value must be unique for all rows with
>parm_code='A'.
>If parm_code != 'A' then the code value does not have to be unique.
>Hence, I cannot create a unique constraint on parm_code and code_value.
>
>Therefore I created a database trigger to check if parm_code = 'A' then
>see if there are any other records with parm_code = 'A' and code_value =
>:new.code_value (and if there is I raise_application_error). The trigger
>works fine for an insert but mutates on an update. Is there a trick to
>doing something like this, or a better way?
>

Chris,

A trigger is the proper way to create this kind of constraint. Since your PL/SQL code works for insert you may just need to tweek it to work for updates. I would need to see the code to help any further.

Nevin Hahn
UCHSC Cancer Center DBA Received on Thu Jan 23 1997 - 00:00:00 CST