Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
UPDATE CHILD RECORDS USING VFP RI GENERATED CODE

Overview
This is a very short article on how to update additional children records within the VFP RI generated code. With the stock RI code generated by Visual FoxPro, you find that the code, if cascade delete is set for update, will update the child key of the parent you have changed. The question comes up, “What if you want to update additional fields in the child records at the same time?”.

How-to
This is quite simple. Set your RI to generate the cascade update as normal. Once this is done you only have to look in the procedure __RI_UPDATE_parent. See below:


PROCEDURE __RI_UPDATE_parent
	** "Referential integrity update trigger for" parent
	LOCAL llRetVal
	llRetVal = .T.
	PRIVATE pcParentDBF,pnParentRec,pcChildDBF,pnChildRec,pcParentID,pcChildID
	PRIVATE pcParentExpr,pcChildExpr
	STORE "" TO pcParentDBF,pcChildDBF,pcParentID,pcChildID,pcParentExpr,pcChildExpr
	STORE 0 TO pnParentRec,pnChildRec
	IF _TRIGGERLEVEL=1
		BEGIN TRANSACTION
		PRIVATE pcRIcursors,pcRIwkareas,pcRIolderror,pnerror,;
			pcOldDele,pcOldExact,pcOldTalk,pcOldCompat,PcOldDBC
		pcOldTalk=SET("TALK")
		SET TALK OFF
		pcOldDele=SET("DELETED")
		pcOldExact=SET("EXACT")
		pcOldCompat=SET("COMPATIBLE")
		SET COMPATIBLE OFF
		SET DELETED ON
		SET EXACT OFF
		pcRIcursors=""
		pcRIwkareas=""
		pcRIolderror=ON("error")
		pnerror=0
		ON ERROR pnerror=rierror(ERROR(),MESSAGE(),MESSAGE(1),PROGRAM())
		IF TYPE('gaErrors(1)')<>"U"
			RELEASE gaErrors
		ENDIF
		PUBLIC gaErrors(1,12)
		PcOldDBC=DBC()
		SET DATA TO ("TRIGGER")
	ENDIF FIRST TRIGGER
	LOCAL lcParentID && parent's value to be sought in child
	LOCAL lcOldParentID && previous parent id value
	LOCAL lcChildWkArea && child work area handle returned by riopen
	LOCAL lcChildID && child's value to be sought in parent
	LOCAL lcOldChildID && old child id value
	LOCAL lcParentWkArea && parentwork area handle returned by riopen
	LOCAL lcStartArea
	lcStartArea=SELECT()
	llRetVal=.T.
	lcParentWkArea=SELECT()
	SELECT (lcParentWkArea)
	pcParentDBF=DBF()
	pnParentRec=RECNO()
	lcOldParentID=OLDVAL("ID")
	pcParentID=lcOldParentID
	pcParentExpr="ID"
	lcParentID=ID
	IF lcParentID<>lcOldParentID
		lcChildWkArea=riopen("child")
		IF lcChildWkArea<=0
			IF _TRIGGERLEVEL=1
				DO riend WITH .F.
			ENDIF at the end of the highest trigger level
			SELECT (lcStartArea)
			RETURN .F.
		ENDIF NOT able TO OPEN the CHILD WORK area
		pcChildDBF=DBF(lcChildWkArea)
		SELECT (lcChildWkArea)
		SCAN FOR ID=lcOldParentID
			pnChildRec=RECNO()
			pcChildID=ID
			pcChildExpr="ID"
			IF NOT llRetVal
				EXIT
			ENDIF && not llretval
			* --- Modifications to RI template by Pete Sass
			* --- Added line of code to enable multiple field
			* --- updating of child records during scanning.
			* --- Note: Use the inhouse riupdate() function to perform
			* --- this task only.
			llRetVal=riupdate("DATETIME",DATETIME(),"CHILD")
			* --- End of the one line modification.
			llRetVal=riupdate("ID",lcParentID,"PARENT")
		ENDSCAN GET ALL OF the CHILD RECORDS
		=rireuse("child",lcChildWkArea)
		IF NOT llRetVal
			IF _TRIGGERLEVEL=1
				DO riend WITH llRetVal
			ENDIF at the end of the highest trigger level
			SELECT (lcStartArea)
			RETURN llRetVal
		ENDIF
	ENDIF THIS PARENT ID changed
	IF _TRIGGERLEVEL=1
		DO riend WITH llRetVal
	ENDIF at the end of the highest trigger level
	SELECT (lcStartArea)
	RETURN llRetVal
ENDPROC

You will notice the code I have added and commented for the purposes in this case is to update the child record with a datetime stamp on the last date and time changed. The only trick to this in this is to use the RI procedure “procedure RIUPDATE”. So, by adding this one line of code:

llRetval=riupdate("DATETIME",DATETIME(),"CHILD") 

as shown above you can accomplish your goal. The riupdate syntax is:

=riupdate("cChildFieldName","What to replace into the field","ChildTableName") 

By adding several lines like this you can update several child fields during the cascade update generated by the VFP RI generator.

Note: The only thing to remember, if you re-generate the RI code you patch will be overwritten, so patch it at the end of the RI generation after you have tested it fully.

ABOUT THE AUTHOR: PETE SASS

Pete Sass Pete has worked in the computer world since the late 70's. He loves the outdoors and lives in a small town called Marathon, in the north of Canada.

FEEDBACK


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: