>Help on understanding INSERT (and UPDATE) with persistant relations in Visual FoxPro (5.0)
>
>Basically this breaks down to a DBC with 2 tables and 1 view combining these.
>
>DBC : Test
>
>Table1 : ParentTable
> UniqueID (Numeric) - Default value -1
> Descript_1 (Character)
> Primary Key : UniqueID, Filter : .NOT. DELETED()
>
>Table2 : ChildTable
> UniqueID (Numeric) - Default value -1
> Descript_2 (Character)
> Primary Key : UniqueID, Filter : .NOT. DELETED()
>
>There is a persistant One-to-One (this is crucial, and can NOT be changed !) relationship
>between the tables, based on the primary key field for both tables (UniqueID).
>
>Relation: ParentTable.UniqueID = ChildTable.UniqueID
>
>
>From my expirience with a couple of other ("true") relational databases I know that cascaded
>INSERT's AND UPDATE's are possible (on the Constraint-level) in these.
>That is : I want to force an Insertion in the ChildTable (updating the UniqueID) whenever there
> is an Insertion in the ParentTable (and preferably the other way also). The Insertion
> has to be a part of the DBC, since I will use the the "rules" as a part of the Data-
> Environment for my application(s). It also has to be bound to the tables since I want
> the cascaded Insertion to happen even if I use a View to perform the Insertion.
>
>I don't think the UPDATE-part is of any concern as long as I use the tables directly since I can solve
>the problem (at least cascading from the ParentTable to the ChildTable, which is the most important)
>with the Referential Integrity Builder (as follows this might not be true using Views).
>
>The INSERT-part on the contrary "plays some tricks on me". First I've tried to use a procedure-call in
>the Insert-trigger for ParentTable looking a little like this :
>
> PROCEDURE InsertChild
> INSERT INTO ChildTable VALUES (ParentTable.UniqueID, "")
> ENCDPROC
>
>This presented me with (at least) two problems :
> Using the tables "Raw" and Inserting by "APPEND BLANK" or "CTRL-Y"
> 1. The Insert-trigger fires at the actual insertion (of course) BEFORE anything but the
> default-value for UniqueID has been put in the Recordbuffer. The inserted Child-records
> UniqueID is therefore assigned the default value for the ParentTable.UniqueID (-1), this
> is no good in multiuser-environments. I realize that this problem can eliminated by using
> a default-value generated by some UDFunction : GetDefaultUniqueID or something like that.
> The road to the solution is long but I can see the end
>
> Defining a View to use for acessing the tables
> 2. When setting up a View (combining ParentTable and ChildTable) and selecting SQL-updates
> including the fields for both tables there appears to be an "UPDATE-CONFLICT" upon inserting
> rows and updating fields. This seems intuitively correct, since an update for the view col-
> lides with the inherited update from the insert-trigger for the ParentTable. However removing
> the insert-trigger for the ParentTable and thus limiting insertions and updates to be per-
> formed through Views presents another problem : If the field ChildTable.UniqueID is left blank
> during insertion of a new row (from the View) the inserted record in ChildTable contains the
> value -1 (default value) in the UniqueID field, whereas the UniqueID in the ParentTable
> contains the correct (input) value of whatever has been input to the field.
>
>This lead to the main Questions :
> Will it at all be possible to do what I want to do ?
> IF YES
> Doesn't the RI-Update-trigger for the ParentTable fire when inserting from a View ?
> (Cascading updates has been choosen for the parent table in the RI-builder)
> If I've misunderstood anything could You please correct me and show me a solution ?
> Is there a way to let update/insertions of the ChildTable reflect in the ParentTable ?
> ELSE
> Why not ?
> Is there another way to accomplish this ?
> ENDIF
>
>
>I hope for ANY help as soon as possible since I'm kind of stuck with this problem and it hunts me
>in my sleeps.
>
>
>Best Regards
> Henrik
Henrik,
Long message and long subject. Let's try to answer partially. If ids are same and one-to-one why not one table (fcount() limit ? - this would catch the view too). Default value does the job well provided it's a unique id (via a stored proc or built-in function) and this cures Ctrl+Y problem.

I never tried cross relate and set RI before for one-to-one. It might well do the job much easier and makes me think it would work.

W/o persistent rels approach. Consider this :
You have default values set to in both tables iif(used("myothertable"),myothertable.uid,sys(2015)). With Ctrl+Y this would simply add a new rec with sys(2015) or othertables.currentuid. w/o no persistent rel. set you could control this in insert trigger and add recs to other table for nonexistent. Similarly in update trigger check oldval() and change uids in other table as well, same goes for delete trigger too (coded RI).

BTW: triggers fire at tableupdate time (immediately if no buffering therefore).
Cetin