Microsoft KB Archive/155749

= FIX: Trigger Failed Error When Updating a Compound Primary Key =

Article ID: 155749

Article Last Modified on 9/30/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition

-



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(" + ",lcParentID,lcParentWkArea)

instead of the following:

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

Make the following modifications to the RIUPDATE routine:

 Add the following additional parameter to the Parameters statement: PARAMETERS tcFieldName,tcNewValue, tcParentWkArea  Add the following variables to the LOCAL declaration line: tcParentTable ncount   Add the following immediately after the LOCAL statement: tcParentTable=ALIAS(tcParentWkArea)  Remark out the current REPLACE command.  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. 

<div class="status_section">

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.

<div class="moreinformation_section">

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
  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 </li> When the Database Designer appears, select persistent relationship.</li> Alternate click the persistent relationship and choose the Referential Integrity Builder.</li> Set the UPDATE to CASCADE.</li> Click OK on the dialog boxes.</li> Return to the Database Designer and Browse the xparent.</li> Modify the contents of either the "half1" or the "half2" field.</li> Press the Down Arrow to move off the record to invoke the Update Trigger.</li> Choose Revert in the Trigger Failed message box.</li> In the Command window issue a DISPLAY MEMORY LIKE GAERRORS.</li> Look on the Visual FoxPro desktop, note the error that caused the Trigger to fail is a syntax error.</li></ol>

Keywords: kbbug kbfix kbvfp500fix KB155749

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.