Firing Order for Rules and Triggers in Visual FoxPro

MORE INFORMATION

Steps to Set Up the Example

1. To duplicate the behavior of the Firing Order of Rules and Trigger, create the sample database container and table with the specified Stored Procedures. Enter the following in the Command window:

      CREATE DATABASE Mydata              && Creates the database container
      MODIFY DATABASE Mydata              && Opens the database container
   							

2. Enter the following Procedures into the Store Procedures:

      PROCEDURE MYCOL
      =MESSAGEBOX("My Column Rule")
   
      PROCEDURE MYROW
      =MESSAGEBOX("My Row Rule")
   
      PROCEDURE MYINS
      =MESSAGEBOX("My Insert Trigger")
   
      PROCEDURE MYDEL
      =MESSAGEBOX("My Delete Trigger")
   
      PROCEDURE MYUPD
      =MESSAGEBOX("My Update Trigger")
   							

3. Enter the following in the Command window:

      CREATE TABLE Mytab(Fname C(15),Lname C(15) UNIQUE CHECK Mycol(), ;
         MI C(2),CHECK Myrow())
   
      CREATE TRIGGER ON Mytab FOR UPDATE AS Myupd()
      CREATE TRIGGER ON Mytab FOR INSERT AS Myins()
      CREATE TRIGGER ON Mytab FOR DELETE AS Mydel()
   							

Steps to Demonstrate Behavior

1. In the Command window, type:

         OPEN DATABASE Mydata
         SET MULTILOCKS ON               && Required for buffering.
         =CURSORSETPROP("Buffering",1)   && Turns Buffering Off
   							

   Or on the Tools menu, click Options, and then click the Data tab and set buffering to Off.
   
   To verify that buffering is Off, enter the following on a new line in the Command window:

         ? CURSORGETPROP("Buffering")
   							

   This returns the value of the Buffering level:

         1=OFF
         2=RECORD(PESSIMISTIC)
         3=RECORD(OPTIMISTIC)
         4=TABLE(PESSIMISTIC)
         5=TABLE(OPTIMISTIC)
   						

2. Highlight the title bar of the table. Then using the right mouse button, click, and select BROWSE.
3. With table buffering Off, press CTRL+Y to append a blank record. Notice that the COLUMN, ROW, UNIQUE check, and INSERT rules fired in order for the new record.
   
   When you press CTRL+Y again, notice that the ROW rule and the UPDATE trigger fire as you leave the first record. Then you once again see the COLUMN, ROW, UNIQUE check, and INSERT rules fired for the new record.
4. Press the UP ARROW key to move off the current record just entered. Notice the ROW, UNIQUE check, and UPDATE rules fire as you leave that record.
5. Mark any record(s) for deletion as necessary. The DELETE trigger fires. Then undelete the records. Notice the firing order of the rules are the same as they are a new record: COLUMN, ROW, UNIQUE check, and INSERT.
   
   As you enter a value or change the value of a LNAME, you will see the "My Column Rule" message box. The firing order was COLUMN RULE, ROW RULE, UNIQUE check, UPDATE TRIGGER.
6. Change Buffering to 2 (Pessimistic Record Locks) by typing the following in the Command window:

         SET MULTILOCKS ON        && If you have not done so previously.
         =CURSORSETPROP("Buffering",2)
   							

   Or on the Tools menu, click options. Then click the Data tab and set buffering to Record(Pessimistic).
   
   Again, press CTRL+Y to append a blank record, and notice that nothing fires. Enter some data. Note COLUMN(S) RULE fires. Leave the record by pressing the UP ARROW. Now, the ROW, UNIQUE check, and INSERT Rules fire in order as you leave the record.
   
   If you mark a record for deletion, the DELETE rule fires. If you undelete a record, the rules fire as before depending on the buffering level you set.
   
   Notice the same behavior occurs for Buffering Level 3, Record(Optimistic).
7. Change Buffering to 4 (Pessimistic Table Locks) by typing the following in the Command window:

         SET MULTILOCKS ON          && If you have not done so previously.
         =CURSORSETPROP("Buffering",4)   && Table(Pessimistic)
   							

   Or on the Tools menu, click options. Then click the Data tab and set buffering to Table(Pessimistic)
   
   Then follow these steps:
   1. Press CTRL+Y to append a blank record, and notice that no rules fire for the new record. Enter some data.
   2. Press CTRL+Y to append a blank record, and notice that the ROW rule fires for the first record. It did not fire for record 2, the new empty record.
   3. Press the UP ARROW key to leave record 2. Notice that the ROW rule fires for record 2 that you are leaving. It did not fire for record 1, the record you went to. This would also be true if you were editing record 5 in a set of 12. If you leave the record by pressing the UP ARROW or DOWN ARROW key, the ROW rule fires for the record you left, not the one you went to. This only happens for new and modified records.
8. Mark record(s) for deletion. There is no problem here, but note that buffering must be turned off before you pack the table or remove the deleted records. To pack, type the following in the Command window:

         =TABLEUPDATE(.t.)
   							

   You will see the DELETE trigger(s) fire as the table is updated from the buffer. You will see the same behavior for buffering level 5. To close the table, use TABLEUPDATE(.t.).
9. Change buffering to 1 (Off) by typing the following in Command window:

         =CURSORSETPROP("Buffering",1)
   							

   Or on the Tools menu, click options. Then click the Data tab and set buffering to OFF
   
   When finished, you can pack or recycle the records.