DOCUMENT:Q131888 15-FEB-2000 [foxpro] TITLE :PRB: Transaction Affects When Rules or Triggers Fire PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:3.0 OPER/SYS: KEYWORDS: ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, version 3.0 ------------------------------------------------------------------------------- SYMPTOMS ======== Rules and triggers defined for a table may not fire at the expected time if a combination of buffering and transaction processing is used. CAUSE ===== This is by design. Rules and triggers fire when an attempt is made to write the data in the buffer to the disk file. RESOLUTION ========== Please see the sample code listed in this article. STATUS ====== This behavior is by design. MORE INFORMATION ================ Before running the following samples, open the Tastrade database located in the \Vfp\samples\mainsamp\data directory, and set the default to this directory. All of the samples use the Customer table from Tastrade, which has a validation rule on the cust_id field. If preferred, you can use the Orders table instead; it has a record validation rule. Before running any sample, issue a SET MULTILOCKS ON command in the Command window. Each sample begins with a TABLEREVERT() command to eliminate any records left in the data buffer from previous examples. This may cause an error if buffering was not in effect before running the example. The error displayed when a field fails a validation rule is 1582. Code Sample One --------------- The validation rule on cust_id never fires when you run this code: =TABLEREVERT() USE customer =CURSORSETPROP("Buffering",3) APPEND BLANK BEGIN TRANSACTION ROLLBACK =TABLEREVERT() The rule never fires because the ROLLBACK and TABLEREVERT commands clear the buffer. Because the blank record is never written to the disk file, the validation rule on cust_id never fires. Code Sample Two --------------- The validation rule fires on the second APPEND BLANK command when you run this code: =TABLEREVERT() USE customer =CURSORSETPROP("Buffering",3) APPEND BLANK BEGIN TRANSACTION ROLLBACK APPEND BLANK This behavior occurs because, even though the ROLLBACK command discarded the current transaction in progress, the record still existed in the buffer. Optimistic row buffering is enabled, and Visual FoxPro automatically attempts to write the first appended record to the table when the record pointer is moved by the second APPEND BLANK command. To prevent this behavior, a TABLEREVERT() should be issued immediately before or after a ROLLBACK command. Code Sample Three ----------------- The validation rule fires once, when the END TRANSACTION command is issued, when you run this code: =TABLEREVERT() USE customer =CURSORSETPROP("Buffering",3) APPEND BLANK BEGIN TRANSACTION END TRANSACTION The transaction doesn't commit to the table, and the record still exists in the buffer. To prevent this behavior, test for successful execution of a TABLEUPDATE command prior to issuing the END TRANSACTION command. Code Sample Four ---------------- The validation rule fires twice when you run this code: =TABLEREVERT() USE customer =CURSORSETPROP("Buffering",3) APPEND BLANK BEGIN TRANSACTION =TABLEUPDATE() END TRANSACTION An error is received only after the END TRANSACTION command is issued. The validation rule fires once for the TABLEUPDATE command. The TABLEUPDATE command fails, but there is no visible indication that the field failed the validation rule. The return value from the TABLEUPDATE command was not trapped, and the END TRANSACTION command attempts to write invalid data to the table. The END TRANSACTION command fires the validation rule a second time, which results in the error 1582. Code Sample Five ---------------- The field validation rule causes the TABLEUPDATE command to fail when you run this code: =TABLEREVERT() USE customer =CURSORSETPROP("Buffering",3) APPEND BLANK BEGIN TRANSACTION llDidUpdate = TABLEUPDATE() IF llDidUpdate END TRANSACTION ELSE lnRecordsDiscarded=TABLEREVERT() WAIT WINDOW STR(lnRecordsDiscarded) + " Records Discarded" ROLLBACK ENDIF The memory variable (llDidUpdate) contains false (.F.), which indicates that the TABLEUPDATE command failed. The validation error never appears on the screen, and a message indicating that one record was discarded appears. This approach demonstrates the recommended approach to combining buffering and transaction processing. The return value from the TABLEUPDATE command should be tested, and the END TRANSACTION command issued only if the TABLEUPDATE command was successful. Additional query words: VFoxWin ====================================================================== Keywords : Technology : kbVFPsearch kbAudDeveloper kbVFP300 Version : WINDOWS:3.0 ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2000.