Re: Mutating triggers

If you have changing the structure of your schema (table definitions, etc.) is possible, then...

There's probably an argument you can make that the parm_code column really represents more than one thing. You have a table that has an attribute (column) that represents whether or not the row is an "A" type row, and you have another attribute that represents whether the row is another type. First normal form says the columns have to be atomic. SQL is built on this assumption, which is why your're having trouble specifying your constraints.

If you could define new table that splits that column, you could then define a constraint that says if it's an "A" row it can't have anything in parm_code and vice versa, and also set a unique constraint for the "A" column combined with the code_value column.

If your question is about a large, existing database, my answer probably isn't too much help. Don't forget that you could probably create a view on the new table that looks like the old table.

If you're building a new application, or your application isn't too big, consider redefining the table.

In article <32E5374E.2ECB_at_lilly.com>, 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 Halioris
 

-- 
Larry Reid                                          lcreid_at_web.net