--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

>
> At 12:51 PM 12/11/2005 +0000, you wrote:
> >When I delete a detail record (say one out of 4), all four detail
> >records are deleted or when I edit a detail record, all the other
> >detail records becomes identical to the edited record.
> >
> >My update, Refresh and delete SQL is as follows:
> >
> >UPDATE JOB_DETAIL
> >SET
> > FIN_YEAR = :FIN_YEAR,
> > CONTRACT_NO = :CONTRACT_NO,
> > ITEM_AMOUNT = :ITEM_AMOUNT,
> > PRICE = :PRICE,
> >WHERE
> > FIN_YEAR = :MAS_FIN_YEAR
> > and CONTRACT_NO = :MAS_CONTRACT_NO
> > and ITEM_AMOUNT = :OLD_ITEM_AMOUNT
> > and PRICE = :OLD_PRICE
> >
> >DELETE FROM
> > JOB_DETAIL
> >WHERE
> > FIN_YEAR = :OLD_FIN_YEAR
> > and CONTRACT_NO = :OLD_CONTRACT_NO
> > and ITEM_AMOUNT = :OLD_ITEM_AMOUNT
> > and PRICE = :OLD_PRICE
> >
> >SELECT
> > FIN_YEAR,
> > CONTRACT_NO,
> > ITEM_AMOUNT,
> > PRICE
> >FROM
> > JOB_DETAIL
> >WHERE( FIN_YEAR = :FIN_YEAR
> >and CONTRACT_NO = :CONTRACT_NO
> > ) and ( JOB_DETAIL.FIN_YEAR = :OLD_FIN_YEAR
> > and JOB_DETAIL.CONTRACT_NO = :OLD_CONTRACT_NO
> > and JOB_DETAIL.ITEM_AMOUNT = :OLD_ITEM_AMOUNT
> > and JOB_DETAIL.PRICE = :OLD_PRICE
> > )
> >
> >My master table contains FIN_YEAR and CONTRACT_NO as a primary

key and

> >I use one transaction for this process. There is no trigger.
> >
> >Like said this works well after restarting the server PC, but

after a

> >while this behaviour happens again.
>
> Depends on what you mean by "works well". Your update and delete

SQL will

> delete all the detail records for the contract and year if their
> item_amount is the same as the input parameter. You need to

target the

> *individual* detail record with *its* primary key, not the primary

key of

> the master.
>
> OTOH, if you are depending on the item_amount to be a unique

identifier for

> each detail record, using a hierarchical PK that is made of that

and the

> foreign key, then you are reaping what you sowed at design time...
>
> ./heLen
>

Helen

I posted a shortened version of my detail table - it is as follows:
UPDATE JOB_DETAIL
SET
FIN_YEAR = :FIN_YEAR,
CONTRACT_NO = :CONTRACT_NO,
PROJ_CODE2 = :PROJ_CODE2,
ITEM_ID = :ITEM_ID,
ITEM_VALUE = :ITEM_VALUE,
ITEM_PRICE = :ITEM_PRICE,
ITEM_AMOUNT = :ITEM_AMOUNT,
PRICE = :PRICE,
COMMENT = :COMMENT
WHERE
FIN_YEAR = :MAS_FIN_YEAR
and CONTRACT_NO = :MAS_CONTRACT_NO
and PROJ_CODE2 = :OLD_PROJ_CODE2
and ITEM_ID = :OLD_ITEM_ID
and ITEM_VALUE = :OLD_ITEM_VALUE
and ITEM_PRICE = :OLD_ITEM_PRICE
and ITEM_AMOUNT = :OLD_ITEM_AMOUNT
and PRICE = :OLD_PRICE

As told previously, it works correctly locally, but in a
client/server situation sometimes all detail records are
deleted\updated so that all the detail records are exactly the same
as the just edited detail record. I am beginning to wonder whether
the problem lies with the FIBPlus components - I will look there and
if the problem lies there - I let you know.

Regards Corlien