Forcing uniqueness using triggers, ORA-4091

Hi all,

i have a special case where i would like to enforce uniqueness using a trigger, because i cannot use a unique constraint.

Consider the following table structure:

number_col1	NOT NULL	NUMBER
number_col2	NOT NULL	NUMBER
number_col3			NUMBER
varchar_col1			VARCHAR(32)

primary key is number_col1 and number_col2.

Now, varchar_col1 is optional but must be unique for number_col1. Normally i would have made a UNIQUE constraint on(number_col1, varchar_col1) but this cannot be done because varchar_col1 may be NULL for several rows.

Whats the most elegant way to do this check? I know that if i create a trigger on the table, i cannot select from the same table because i will run into the ever annoying mutating table problem (ORA-4091).

Any hints will be greatly appreciated.

Happy new year to you all,

Brian

--
Brian Rasmusson
Lead Software Engineer
Belle Systems
E-mail: br_at_belle.dk
Tel.: +45 5944 2500 Received on Wed Dec 30 1998 - 09:36:05 CST