Re: Trigger(?) question

On 12 Jan 1998 08:30:42 GMT, "Frédéric Trébuchet" <frederic.trebuchet_at_sncf.fr> wrote:

>Hello,
>
>If you want to ensure that there is no more than one row with status ACTIVE
>for a given OLD_PRODUCT_ID value, you can also define a UNIQUE INDEX using
>these two columns (OLD_PRODUCT_ID, STATUS). This way, you do not have to
>code any trigger, just trap error code after any insert/update statement.
>You have to change the column STATUS to a numeric one same as PRODUCT_ID
>then, just say, if ACTIVE then STATUS equal 0 else STATUS = PRODUC_ID.
>

there is no need that STATUS is a number field; you can combine fields with different types in one query.

what i would do is:
set status = 1 for active and status = null for inactive.

if you do it like this you can have as many equal OLD_PRODUKT_ID's as you want as long as there STATUS is null. because null is not equal to null !!
if you have two rows with the same poduct id and the status = 1 the unique index is violated.

>If you want to see the value ACTIVE or INACTIVE when selecting, you may use
>the decode function.

if you print it you can fill in active and inactive by a support table
>
>Hope this helps,

hope it helps better
olaf Received on Mon Jan 12 1998 - 00:00:00 CST