I have developed a unique (?) working model of a
Master/Detail/Detail/Detail/Detail/Detail (etc.)relationship which I am prepared
to download to anyone interested. Provided they can assist with a few REMAINING problems - listed below.
REQUIRED:
4 tables called PARENT, CHILD, GCHILD and GGCHILD wherein it is essential that the
first child (C_NO in CHILD) of each parent (P_NO in PARENT) MUST be numbered "1". As must
also be the case with the first grand-child (GC_NO in GCHILD) etc..
TWO PROBLEMS AT OUTSET
1.
I cannot develop such a relationship using .DB tables and TTable Object from the Delphi VCL as,
Whilst the Referential Integrity between tables are perfect for what I need, they do not
(to begin with) provide for more than a Master/Detail/Detail relationship - which is too
limiting for my needs.
2.
Whilst I can use .GDB tables and SQL (as an alternative) they do not overcome Problem 1 above
either - if one follows the normal (?) approach.
In fact then I am worse off. In that I cannot even have two children (in a tbl CHILD) EACH numbered "1" (in a field C_NO) for each of two parents (in fields P_NO) in a tblPARENT - as is at least possible with the former option. Because C_NO must (of necessity) be a Unique field
in order that GC_NO (from tbl GPARENT) can relate to it in the .GDB table.
THE SOLUTION
1.
Using a .GDB Table and purely to provide an Index and prevent key-violations in each table I have a field CID_NO (for tbl CHILD), field GCID_NO (for tbl GCHILD), etc..
(At which point I might mention that the following will NOT work.
SELECT *
FROM CHILD t1, PARENT t2
WHERE t1,P_NO =: T2.P_NO
As it merely serves to provide ANY AND ALL records in (say) CHILD which have a P_NO in PARENT. In fact ALL them - regardless of the value of P_NO pointed to in PARENT at any given moment.
2.
I have an intermediate tbl TRNSFR (which must NEVER have more than ONE record) to which ALL the tables can easily relate as it has fields P_NO, C_NO, GC_NO GGC_NO.
3.
Using the AfterScroll of the relevant table the value of P_NO (of PARENT) C_NO (of CHILD, etc.) can be posted to TRNSFR the moment the pointer moves.
4.
NOW the following provides the required result:
(Bearing in mind that I am involved with Delphi 6, Interbase 6 and the ThinClient of a 3-Tier application.)
4.1
procedure TParentDM.IBdsChildAfterScroll(DataSet: TDataSet);
begin
with ParentDM.IBdsTrnsfr do
begin
ParentDM.IBdsTrnsfr.Active := True;
if ParentDM.IBdsTrnsfr.state in dsEditModes then
else
ParentDM.IBdsTrnsfr.edit;
ParentDM.IBdsTrnsfrC_NO.value := ParentDM.IBdsCHILDC_NO.value;
ParentDM.IBdsTrnsfr.ApplyUpdates;
end;
end;
4.2 In ModifySQL of IBDataSet I have
SELECT *
FROM CHILD t1,TRNSFR t2
WHERE t1.P_NO = t2.P_NO
(From which the reason for never having more than ONE record in TRNSFR becomes obvious.)
PROVIDED THAT one has a TButton EventHandler
procedure TfrmFather.btnUpdChildClick(Sender: TObject);
begin
ParentDM.IBdsTrnsfr.ApplyUpdates;
ParentDM.IBFatherDataSet.ApplyUpdates;
ParentDM.IBdsChild.ApplyUpdates;
end;
BECAUSE the desired effect from 4.1 is delayed.
(i.e. AfterScroll of having moved TO (say) P_NO 2 only becomes evident upon doing an AfterScroll a SECOND time - to anywhere.)
And accordignly one HAS TO rely on the TButton above to UpDate the movement of the pointer in PARENT.
REMAINING PROBLEMS.
1.
Overcoming the delay-effect mentioned above. This creating
reliance on the TButton. Instead of it happening automatically with AfterScroll - the FIRST time.
2.
If (to overcome 1. above) the statement (below) from the TButton EventHandler is included to follow the code in 4.1 above (where it belongs) I get the following exception.
ParentDM.IBdsChild.ApplyUpdates;
'IBdsChild: Cannot perform this operation on a closed dataset'
3.
A Delete of a record in (say) CHILD which still has related records in GCHILD etc.. should be prevented.
Any takers.
