Re: Trigger-question

 >From: Erik van der Looy  

>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

A trigger only knows about the row that is being updated. There are 2 possible methods that might work.
If the data design allows it, you might be able to define a unique key on the combination OLD_PRODUCT_ID and STATUS. This will not work if multiple active products can have the same OLD_PRODUCT_ID. The other way is to write a procedure that is used to insert/update the row. The procedure can look at other rows in the table, then do the desired update.

Dan Received on Tue Jan 13 1998 - 00:00:00 CET