KB147199 - How To Determine If Insert Was Successful

Article ID: 147199

Article Last Modified on 7/1/2004

Microsoft Visual FoxPro 3.0 Standard Edition
Microsoft Visual FoxPro 3.0b Standard Edition
Microsoft Visual FoxPro 5.0 Standard Edition
Microsoft Visual FoxPro 5.0a
Microsoft Visual FoxPro 6.0 Professional Edition

This article was previously published under Q147199

SUMMARY

The implementation of Referential Integrity (RI) in Visual FoxPro uses user defined field and record rules in a database. You can set up rules manually or you can use the RI builder to control how records are inserted, updated, or deleted in related tables. This article describes two methods you can use to verify that an INSERT, DELETE, or UPDATE trigger was successful.

MORE INFORMATION

If you are working directly with a table in a database, error 1539 (Trigger failed) is generated when a trigger fails. Your code can check for this error in an error handling routine. This method only provides the information that the trigger failed. It requires the existence of a variable to track the origin of the failure.

Another way to find out if a trigger failed is to work with buffered data to verify that the data can be committed. This method provides more flexibility than the previous one. The TABLEUPDATE() function commits changes made to buffered data. It returns false (.F.) when Visual FoxPro cannot successfully commit changes to a record. You can check the result of TABLEUPDATE() to confirm that the data is committed to the table, and if TABLEUPDATE() returns false (.F.), you can call the AERROR() function to verify that the trigger failed. The following sample code gives a simple illustration of using AERROR() with the TABLEUPDATE() function:

   SELECT 0

   *-- In Visual FoxPro 3.0:
   *-- USE SYS(2004)+"samples\mainsamp\DATA\customer"
   *-- In Visual FoxPro 5.0:
   *-- USE SYS(2004)+"samples\tastrade\data\customer"
   *-- In Visual FoxPro 6.0:
   USE home(2) + "\tastrade\data\customer"

   SET MULTILOCK ON
   =cursorsetprop('Buffering', 5)  && Optimistic Table buffering
   LOCATE FOR customer_id="ANTON"
   DELETE     && this will fail because there are child records

   LUPDATE=TABLEUPDATE(.T.)

   IF LUPDATE=.F.
      =TABLEREVERT()
      =MESSAGEBOX('did not update')
   =AERROR(aErrorArray)
   DO CASE
      CASE aErrorArray(1,5)=1
         cMessage="Insert"
      CASE aErrorArray(1,5)=2
         cMessage="Update"
      CASE aErrorArray(1,5)=3
         cMessage="Delete"
   ENDCASE
   ? cMessage
   ?? " "
   ?? aErrorArray(1,2)
   ENDIF
   USE

If you need more precise information than AERROR() provides and you have used the RI builder to build the insert, update, or delete rules, you can inspect the values that are stored in the gaError array. This array is built by the RI builder. When an error occurs, the array is populated with information about the error and about the tables and record for which the trigger failed.

For more information about gaErrors, please see the following article in the Microsoft Knowledge Base:

142284 How to Use gaErrors() to Find Out Why a Trigger Failed

Keywords: kbhowto kbdatabase KB147199