conIBO is a TIBODatabase component with tiCommitted isolation

Yes I have UpdateSQL on qrTbBeliD, the code is generate automatically by
TIBOQuery feature.

The "id" field of tbBeliD.ID is never visible to users when they using this
program. So the user cann't make it null when it already has a value from
generator "genTbBeliD". Yes I fill tbBeliD.ID on event OnBeforePost of
qrTbBeliD, the value from this OnBeforePost event is never wrong.

The problem is when user add a new record of qrTbBeliD, the value of
tbBeliD.ID is automatically filled by tbBeliM.ID which is I don't write
any single code from Delphi or from trigger of that table to make this
happen. That looks like TIBOQuery automatically do that.

If it's automatically do by TIBOQuery then it's wrong because the relation
between the master (qrTbBeliM) and the detail (qrTbBeliD) is on "id" field
of qrTbBeliM and the "idTbBeliM" field of qrTbBeliD.

I put qrTbBeliM and qrTbBeliD in a master detail relationship (both
qrTbBeliM and qrTbBeliD are the name of TIBOQuery component).
The master detail relationship that I do is:
1. Set the datasource property of qrTbBeliD to the TDatasource of qrTbBeliM
2. Make the SQL property of qrTbBeliD to have a param to "id". Here is the
SQL property of qrTbBeliD:
select d."id", d."idTbBeliM", d."idTbBarang", d."idTbGudang", d."qty",
d."satuan", d."konversi", d."qtyKecil",
d."hargaSat", d."disc", d."subTotal", d."ongkosKirim",
b."kode", b."nama", b."partNumber", b."satKecil"
from "tbBeliD" d
join "tbBarang" b on b."id"=d."idTbBarang"
where d."idTbBeliM"=:"id" // ====> Here the SQL that said that this
qrTbBeliD "id" param refer to qrTbBeliM.ID
order by d."id"


Hope make you clear at this point. Thank you for your patience. I'm from
Indonesia. TIA

----- Original Message -----
From: russellbelding
To: IBObjects@yahoogroups.com
Sent: Saturday, May 05, 2012 5:30 AM
Subject: [IBO] Re: PK field that auto filled with wrong generator value

Hello Cipto

There are some details in your post I do not get. What is conIBO? I don't
want to guess.

Other details could influence the problem. In your TIBOQuery named qrTbBeliD
do you have Update SQL?

If tbBeliD.ID is the primary key for table tbBeliD then when qrtbBeliD
records will never be null at the time they are loaded into your client
program. The OnPostEvent will succeed in changing the ID
value only if this value is null. So the ID value will change by this event
only if it has been edited from a value to null.

When your client adds a new record to table tbBeliD by entering a new record
into qrtbBeliD and if the ID value is null then when this new record is
posted the event fills the ID value from generator gentbBeliD. Is this what
is happening? If so how are you determining that the ID value is wrong?

Maybe my comments will prompt some thought to you that leads to insight. (I
don't get the full picture.)

HTH.

--- In IBObjects@yahoogroups.com, "Cipto" <cipto.milis@...> wrote:
>
> Sorry for my bad English. That's not what I mean.
>
> I don't have any trigger on "tbBeliD" table. Yes, the d."id" is the
> primary
> key, and I will assign it's value from Delphi (OnBeforePost event of
> qrTbBeliD (TIBOQuery component)) with the same meaning code as you write
> from trigger:
>
> if qrTbBeliDid.IsNull then
> qrTbBeliDid.AsLargeInt:=conIBO.Gen_ID('"genTbBeliD"', 1);
>
>
> The question is when the program add a new record of qrTbBeliD, why the
> "id"
> field of qrTbBeliD always auto filled with "id" field from qrTbBeliM?
> There
> is no single code on my trigger or on OnNewRecord event of qrTbBeliD that
> assign that value. It's automatically filled by the TIBOQuery component
> and
> it's wrong.
>
>
> ----- Original Message -----
> From: russellbelding
> To: IBObjects@yahoogroups.com
> Sent: Friday, May 04, 2012 3:14 PM
> Subject: [IBO] Re: PK field that auto filled with wrong generator value
>
> When you use event OnBeforePost the action is taken before the TIBO_NN
> object has data changes posted. Each time you change the table used and
> save
> the changes a post is done. So the field D.ID is changed each time the
> table
> changes.
>
> If D.ID is the primary key for your table and you want a unique key in
> this
> field each time you insert a record you could use a "before insert"
> trigger
> on the table.
>
> if (new.ID is null) then
> new.ID = gen_id(generatorname, 1);
>
> > > I also have a generator called "genTbBeliD".
> > >
> > > Now the problem is:
> > > Everytime I insert a new record on "tbBeliD" (just still in insert
> > > condition, before post) why the field d."id" is automatically filled
> > > with an integer value that I don't know where it come from??
> > >
> > > I'm not set any propoerty of TIBO_Query or TIBO_Database to
> > > automatically fill the "id" field from any generator, because I
> > > explicitly fill d."id" value onBeforePost of TIBO_Query with this
> > > statement:
> > > if qrTbBeliDid.IsNull then
> > > qrTbBeliDid.AsLargeInt:=conIBO.Gen_ID('"genTbBeliD"', 1);
> > >
> > > Any clue??
> > >
> > > TIA