PRB: Trigger Fails with One-to-Many Form Wizard and RI Builder (137278)

SYMPTOMS

The following sequence of events results in this error:

Failed to update table: Trigger Failed

Sequence of Events Leading to Error

Use the Referential Integrity (RI) Builder to set the Insert Trigger of the child table to Restrict.
Use the One-to-Many Form Wizard to create a form.
Run the form, and click Add, then click Add Record to Both in the Add Record dialog box, and save the form.

CAUSE

When Visual FoxPro opens the tables, it opens the parent first and then the child. The Parent table is opened in work area one. When the child table is opened, the parent is moved to work area two, and the child is opened in work area one.

The UpdateRows method in Wizstyle.vcx handles updating the tables. It uses the AUSED() function to place the names of the tables into an array and a For loop to cycle through each table and append the new record. Because the record is appended to the child table first (because of its order in the array and work area), the trigger cannot find a matching record in the parent, so it fails. If the the record was appended to the parent table first, the problem would not occur.

RESOLUTION

You can modify the code in the UpdateRows method of the txtbtns class of Wizstyle.vcx. Find the line that reads:

   FOR I = 1 TO m.nTablesUsed

Change it to this:

   FOR I = m.nTablesUsed TO 1 STEP -1

This causes the record to be appended to the parent table first, so the Insert trigger will see a corresponding record in the parent when it updates the child.

MORE INFORMATION

Steps to Reproduce Behavior

Create a database called test, and add two tables (a parent and a child, also known as master and detail) to the database giving each a field in common such as Id.
Set the index on the parent table to Primary, and the index on the child table to Candidate. Create a persistent relationship between the tables.
Start the Referential Integrity Builder. Change the Insert rule to Restrict, and save the database.
Start the One-to-Many Form Wizard, and follow these steps:
1. Select the fields in the parent (step 1).
2. Select the fields in the child (step 2).
3. Take the default relationship (step 3).
4. Select any button style you want (step 4).
5. Don't specify a sort order (step 5). None is needed.
6. Save and run the Form (step 6).
When the form is running Click Add. The click Add Record to Both in the Add Record Dialog box.
Type a value in the Key Value text box.
Click Add, and then click Save. At this point, the error occurs:
Failed to update Table: Trigger Failed
Change the line of code as previously described, and rerun the form. Everything now works correctly.