Re: Trigger(?) question

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.

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

Hope this helps,

TRF Erik van der Looy <evdlooy_at_iaehv.nl> a écrit dans l'article <34B5FE32.C0DF469F_at_iaehv.nl>...
> Hello experts,
>
> I've got a problem with creating a trigger, or maybe it
> would be better to solve the problem on another way(?).
> If somebody could give it a go, I would really appreciate
> the help;
>
> On a certain table there are the next columns:
>
> PRODUCT_ID(PK) STATUS OLD_PRODUCT_ID
> ---------- -------- --------------
> 1 INACTIVE 254
> 2 INACTIVE 259
> 3 ACTIVE 280 *
> 4 INACTIVE 312
> 5 INACTIVE 280 *
> 6 ACTIVE 129
>
> When a status is set from INACTIVE to ACTIVE, there
> must be a check (trigger?) to see if the value in OLD_PRODUCT_ID
> is unique in the column OLD_PRODUCT_ID.
> For example the rows indicated with *. So the trigger must
> go off afther changing the status in row 5 to ACTIVE.
>
> I think this shouldn't be too difficult, but one way
> or another, I can't get it too work!
>
> Any help is appreciated, preferred by mail.
> Thanks in advance!
>
> Regards,
> Erik
>
>
>
>
>
Received on Mon Jan 12 1998 - 00:00:00 CST