Knowledge Base

FIX: Trigger Failed Error When Updating a Compound Primary Key

Article ID: 155749

Article Last Modified on 9/30/2003

APPLIES TO

This article was previously published under Q155749

SYMPTOMS

When you make a change to a column in a table that makes up a portion of a primary key or a compound primary key, and the Referential Integrity Builder has referential integrity in place, the Cascade Update to the Child table fails with a "Trigger Failed."

CAUSE

The following REPLACE command in the RIUPDATE procedure improperly evaluates out to "FieldName1+FieldName2":

REPLACE (tcFieldName) WITH tcNewValue

This is why looking at the GaError array, which is filled by the Referential Integrity Builder, reveals the trigger failed because of a syntax error.

WORKAROUND

To work around this problem, go into the Stored Procedures and modify the call to the RIUPDATE routine in the procedure called "__RI_UPDATE_your_table_name" so that it sends an additional argument of "lcParentWkArea." It should then read the following:

llRetVal=riupdate("<fieldname1>+<fieldname2>",lcParentID,lcParentWkArea)

instead of the following:

llRetVal=riupdate("ONE+TWO",lcParentID)

Make the following modifications to the RIUPDATE routine:

  1. Add the following additional parameter to the Parameters statement: PARAMETERS tcFieldName,tcNewValue, tcParentWkArea
  2. Add the following variables to the LOCAL declaration line: 
       tcParentTable
   ncount
  3. Add the following immediately after the LOCAL statement: 
       tcParentTable=ALIAS(tcParentWkArea)
  4. Remark out the current REPLACE command.
  5. Add the following code immediately after the commented REPLACE command: 
       ncount=OCCURS('+',tcFieldName)+1
   FOR i = 1 TO ncount
     DO CASE
      CASE i = 1 && First occurrence of "+"
       REPLACE (SUBSTR(tcFieldName,i,AT("+",tcFieldName,i)-1));
       WITH EVAL(tcParentTable+'.'+;
       SUBSTR(tcFieldName,i,AT("+",tcFieldName,i)-1))
      CASE i = ncount && Last occurrence of "+"
       REPLACE (SUBSTR(tcFieldName,AT('+',tcFieldName,ncount-1)+1)) ;
       WITH EVAL(tcParentTable+'.'+ ;
       SUBSTR(tcFieldName,AT('+',tcFieldName,ncount-1)+1))
      OTHERWISE && Any number of occurrences of + in between.
       REPLACE (SUBSTR(tcFieldName,AT('+',tcFieldName,(i-1))+1, ;
       ((AT('+',tcFieldName,i)-AT('+',tcFieldName,(i-1)))-1))) ;
       WITH EVAL(tcParentTable+'.'+SUBSTR(tcFieldName, ;
       AT('+',tcFieldName,(i-1))+1,((AT('+',tcFieldName,i)- ;
       AT('+',tcFieldName,(i-1)))-1)))
     ENDCASE
   NEXT i
    The above code works with any number of columns in a compound key.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem has been fixed in Visual FoxPro 5.0 for Windows.

MORE INFORMATION

This problem is limited to FOREIGN KEYs, based on expressions that use the concatenation character, "+", or complex expressions in the RIUPDATE routine.

Steps to Reproduce Behavior

  1. Copy and Paste the following into a program and run it: 
        CREATE DATABASE test
    CREATE TABLE xparent (half1 c(3), half2 c(3), other c(5))
    ALTER TABLE xparent ADD PRIMARY KEY half1+half2 TAG pkey
    CREATE TABLE xchild (half1 c(3), half2 c(3), other c(5), ;
       FOREIGN KEY half1+half2 TAG fkey REFERENCES xparent )
    CLOSE TABLES ALL
    USE xparent
    INSERT INTO xparent (half1, half2, other) VALUES ('aaa','111','a1')
    INSERT INTO xparent (half1, half2, other) VALUES ('bbb','222','b2')
    INSERT INTO xparent (half1, half2, other) VALUES ('ccc','333','c3')
    USE xchild
    INSERT INTO xchild (half1, half2, other) VALUES ('aaa','111','a1')
    INSERT INTO xchild (half1, half2, other) VALUES ('bbb','222','b1')
    INSERT INTO xchild (half1, half2, other) VALUES ('ccc','333','c1')
    INSERT INTO xchild (half1, half2, other) VALUES ('bbb','222','b2')
    INSERT INTO xchild (half1, half2, other) VALUES ('aaa','111','a2')
    INSERT INTO xchild (half1, half2, other) VALUES ('aaa','111','a3')
    MODIFY DATA NOWAIT
  2. When the Database Designer appears, select persistent relationship.
  3. Alternate click the persistent relationship and choose the Referential Integrity Builder.
  4. Set the UPDATE to CASCADE.
  5. Click OK on the dialog boxes.
  6. Return to the Database Designer and Browse the xparent.
  7. Modify the contents of either the "half1" or the "half2" field.
  8. Press the Down Arrow to move off the record to invoke the Update Trigger.
  9. Choose Revert in the Trigger Failed message box.
  10. In the Command window issue a DISPLAY MEMORY LIKE GAERRORS.
  11. Look on the Visual FoxPro desktop, note the error that caused the Trigger to fail is a syntax error.

Keywords: kbbug kbfix kbvfp500fix KB155749


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.