Re: Forcing uniqueness using triggers, ORA-4091

Brian,
You must have 3 tables
TABLE1
number_col1 NOT NULL NUMBER
...............
Where number_col1 is primary key
TABLE2
number_col1 NOT NULL NUMBER
number_col2 NOT NULL NUMBER
.............
Where number_col1 and number_col2 are primary key, and number_col1 is foreign key from table1
TABLE3
number_col1 NOT NULL NUMBER
varchar_col1 VARCHAR(32)
Where number_col1 and varchar_col1 are primary key, and number_col1 is foreign key from table1
On this table there are only rows with field varchar_col1 not null.

Hope this help
Miguel

Brian Rasmusson wrote in message <368A4865.E8FD0BDF_at_belle.dk>...
>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 Fri Jan 08 1999 - 04:51:56 CST