Progress
Programming
Handbook


Transactions and Triggers

If a transaction begins with a trigger, it cannot extend beyond the end of the trigger. If the transaction is scoped to a block within the trigger, the normal transaction scoping rules apply. If the trigger is scoped to the trigger block itself, the transaction ends when the trigger ends.

In the procedure p-txn12.p, a transaction occurs within the CHOOSE trigger for the upd–cust button and within the CHOOSE trigger for the del–cust button:

p-txn12.p
DEFINE BUTTON upd-cust LABEL "Update Customer".  
DEFINE BUTTON del-cust LABEL "Delete Customer". 
DEFINE BUTTON exit-app LABEL "Exit". 
DEFINE VARIABLE curr-cust AS ROWID. 
DEFINE VARIABLE changes-made AS LOGICAL. 
DEFINE QUERY seq-cust FOR customer. 
DEFINE BROWSE brow-cust  
   QUERY seq-cust  
   DISPLAY Cust-num Name  
WITH 10 DOWN. 
FORM 
   upd-cust del-cust exit-app SKIP(1) 
   brow-cust 
   WITH FRAME main-frame. 
OPEN QUERY seq-cust FOR EACH customer. 
ON VALUE-CHANGED OF brow-cust 
   DO: 
      curr-cust = ROWID(customer). 
   END. 
ON CHOOSE OF upd-cust 
   DO: /* TRANSACTION */ 
      FIND customer WHERE ROWID(customer) = curr-cust EXCLUSIVE-LOCK. 
      UPDATE customer WITH FRAME cust-frame VIEW-AS DIALOG-BOX 
         TITLE "Customer Update". 
      changes-made = brow-cust:REFRESH(). 
      RELEASE customer. 
   END. 
ON CHOOSE OF del-cust 
   DO:  
       MESSAGE "Delete" customer.name + "?" VIEW-AS ALERT-BOX 
           QUESTION BUTTONS YES-NO UPDATE kill-it AS LOGICAL. 
       IF kill-it 
       THEN DO TRANSACTION: 
          FIND customer WHERE ROWID(customer) = curr-cust EXCLUSIVE-LOCK.  
  
          DELETE customer. 
          changes-made = brow-cust:REFRESH(). 
       END. 
   END.  
ENABLE ALL WITH FRAME main-frame. 
PAUSE 0 BEFORE-HIDE. 
WAIT-FOR CHOOSE OF exit-app OR WINDOW-CLOSE OF DEFAULT-WINDOW. 

The transaction for updating a customer is scoped to the trigger block for CHOOSE of upd–cust. The transaction begins and ends when the trigger begins and ends. The transaction for deleting a customer is scoped to a DO block within the CHOOSE trigger for del–cust. The transaction begins and ends when the DO block begins and ends.

If a transaction is started in the main code and is active when a trigger is invoked, the trigger becomes part of the transaction. You cannot end the transaction within a trigger. For example, the procedure p-txn13.p begins a transaction for each pass through the DO WHILE loop (trans–loop) in the main code. All updates and deletions performed within the loop are part of a single transaction. When you choose either the tran–com or tran–undo button, the loop either iterates or is undone. At that point, all changes made during that iteration are either committed or backed out.

p-txn13.p
DEFINE BUTTON upd-cust LABEL "Update Customer". 
DEFINE BUTTON del-cust LABEL "Delete Customer". 
DEFINE BUTTON exit-app LABEL "Exit". 
DEFINE BUTTON tran-undo LABEL "Undo Transaction". 
DEFINE BUTTON tran-com LABEL "Commit Transaction". 
DEFINE VARIABLE changes-made AS LOGICAL. 
DEFINE VARIABLE curr-cust AS ROWID. 
DEFINE VARIABLE exit-chosen AS LOGICAL. 
DEFINE VARIABLE undo-chosen AS LOGICAL. 
DEFINE QUERY seq-cust FOR Customer. 
DEFINE BROWSE brow-cust QUERY seq-cust  
   DISPLAY Cust-num Name WITH 10 DOWN. 
FORM  
   upd-cust del-cust exit-app SKIP(1) 
   brow-cust SKIP(1) 
   tran-com tran-undo 
   WITH FRAME main-frame. 
OPEN QUERY seq-cust FOR EACH Customer. 
ON VALUE-CHANGED OF brow-cust 
   DO: 
      curr-cust = ROWID(Customer). 
   END. 
ON CHOOSE OF upd-cust 
   DO: 
      FIND Customer WHERE ROWID(Customer) = curr-cust EXCLUSIVE-LOCK. 
      UPDATE Customer WITH FRAME cust-frame VIEW-AS DIALOG-BOX 
         TITLE "Customer Update". 
      changes-made = brow-cust:REFRESH(). 
   END. 
ON CHOOSE OF del-cust 
   DO:  
       MESSAGE "Delete" Customer.name + "?" VIEW-AS ALERT-BOX 
           QUESTION BUTTONS OK-CANCEL UPDATE kill-it AS LOGICAL. 
       IF kill-it 
       THEN DO: 
          FIND Customer WHERE ROWID(Customer) = curr-cust EXCLUSIVE-LOCK. 
          DELETE Customer. 
          changes-made = brow-cust:REFRESH(). 
       END. 
   END.  
ON CHOOSE OF tran-undo 
  DO: 
     undo-chosen = TRUE.  
  END. 
ON CHOOSE OF exit-app 
  DO: 
     DEFINE BUTTON exit-commit LABEL "Commit" AUTO-GO. 
     DEFINE BUTTON exit-undo LABEL "Undo" AUTO-GO. 
     DEFINE BUTTON exit-cancel LABEL "Cancel" AUTO-ENDKEY. 
     FORM 
        "Do you want to commit or undo your changes?" SKIP 
         exit-commit exit-undo exit-cancel 
         WITH FRAME exit-frame VIEW-AS DIALOG-BOX TITLE "Exit". 
     ON CHOOSE OF exit-undo 
        DO: 
           undo-chosen = TRUE. 
        END. 
     exit-chosen = TRUE. 
     /* If changes have been made during the current transaction, 
        then ask the user to either commit or undo them (or cancel 
        the Exit operation).                                       */ 
     IF changes-made 
     THEN UPDATE exit-commit exit-undo exit-cancel WITH FRAME exit-frame. 
  END. 
ENABLE ALL WITH FRAME main-frame. 
PAUSE 0 BEFORE-HIDE. 
exit-chosen = FALSE.  
trans-loop: 
DO WHILE NOT exit-chosen TRANSACTION ON ENDKEY UNDO, LEAVE 
                    ON ERROR UNDO, LEAVE: 
   changes-made = brow-cust:REFRESH(). 
   ASSIGN changes-made = FALSE 
          undo-chosen = FALSE. 
   WAIT-FOR CHOOSE OF tran-com, tran-undo, exit-app. 
   /* If the user chose UNDO (either from the main frame 
      or from the Exit dialog), then undo the current 
      transaction and either start a new one or exit.    */ 
   IF undo-chosen  
   THEN DO: 
      IF exit-chosen 
      THEN UNDO trans-loop, LEAVE trans-loop. 
      ELSE UNDO trans-loop, RETRY trans-loop.  
   END. 
   /* Make sure we don’t hold any locks after committing a transaction. */ 
   IF AVAILABLE(Customer) 
   THEN RELEASE Customer. 
   END. 

When you choose the exit–app button from the main screen, the DO WHILE loop completes and the last transaction is committed.

NOTE: Generally, you do not want transactions to span triggers. Instead, you should design your application so that each transaction occurs within a single trigger. Otherwise, undoing one action might cause other unrelated actions to be undone also.

Transactions are, by nature, modal. This creates conflicts when you try to write a modeless application. You can handle this is one of two ways:

The procedure p-txn14.p allows what appears to be a modeless update:

p-txn14.p
DEFINE BUTTON exit-app LABEL "Exit". 
DEFINE BUTTON com-cust LABEL "Save Changes". 
DEFINE BUTTON rev-cust LABEL "Revert to Saved". 
DEFINE VARIABLE curr-cust AS ROWID. 
DEFINE VARIABLE exit-chosen AS LOGICAL. 
DEFINE VARIABLE rev-chosen AS LOGICAL. 
DEFINE VARIABLE temp-hand AS WIDGET-HANDLE. 
DEFINE BUFFER this-cust FOR Customer. 
DEFINE QUERY seq-cust FOR this-cust SCROLLING. 
DEFINE BROWSE brow-cust QUERY seq-cust DISPLAY Cust-num Name WITH 4 DOWN. 
FORM 
   exit-app SKIP 
   brow-cust 
   WITH FRAME main-frame. 
FORM 
   Customer.Cust-num Customer.Name Customer.Address Customer.Address2  
   Customer.City Customer.State Customer.Postal-Code Customer.Country 
   Customer.Phone Customer.Contact Customer.Sales-rep  
   Customer.Credit-Limit Customer.Balance Customer.Terms 
   Customer.Discount Customer.Comments 
   SKIP 
   com-cust AT 15 rev-cust AT 45 
   WITH FRAME curr-frame SIDE-LABELS. 
OPEN QUERY seq-cust FOR EACH this-cust.  
FIND FIRST Customer NO-LOCK. 
ON ITERATION-CHANGED OF brow-cust 
   DO: 
      IF AVAILABLE(Customer) 
      THEN DO: 
         /* Determine whether any updates were made to previous record. */ 
         temp-hand = FRAME curr-frame:CURRENT-ITERATION. 
         temp-hand = temp-hand:FIRST-CHILD. 
         search-widgets:  
         DO WHILE temp-hand <> ?:  
            IF CAN-QUERY(temp-hand, "MODIFIED") 
            THEN IF temp-hand:MODIFIED 
                 THEN LEAVE search-widgets. 
            temp-hand = temp-hand:NEXT-SIBLING. 
         END. 
         /* If a modification was made, assign the record. */ 
         IF temp-hand <> ? 
         THEN DO: 
            DO WITH FRAME curr-frame TRANSACTION:  
               ASSIGN Customer. 
            END. 
            MESSAGE "Customer record updated.". 
         END. 
      END. 
      /* Set curr-cust to the ROWID of the current query 
         record. Find that record with NO-LOCK.           */  
      curr-cust = ROWID(this-cust).  
      FIND Customer WHERE ROWID(Customer) = curr-cust NO-LOCK. 
      /* Display the current record. We have to disable the update 
         fields and them re-enable them after the DISPLAY. Otherwise, 
         the DISPLAY sets all the MODIFIED attributes to TRUE.*/ 
      DISABLE ALL WITH FRAME curr-frame. 
      DISPLAY Customer WITH FRAME curr-frame.  
      ENABLE ALL WITH FRAME curr-frame.  
   END. 
ON CHOOSE OF exit-app /* Exit application. */ 
   DO: 
      /* Set a flag so we’ll know why we exited the WAIT-FOR. */ 
      exit-chosen = TRUE. 
   END. 
ON CHOOSE OF com-cust /* Commit changes to Customer record. */ 
   DO: 
      /* Commit any changes made to the Customer record. */ 
      DO WITH FRAME curr-frame TRANSACTION: 
         ASSIGN Customer. 
         RELEASE Customer. 
      END. 
      MESSAGE "Customer record updated.". 
      /* Release the exclusive lock. */  
      CLOSE QUERY seq-cust. 
      OPEN QUERY seq-cust FOR EACH this-cust. 
      REPOSITION seq-cust TO ROWID curr-cust. 
      /* Restore the MODIFIED attribute to FALSE. */ 
      HIDE FRAME curr-frame. 
      DISABLE ALL WITH FRAME curr-frame. 
      ENABLE ALL WITH FRAME curr-frame. 
      VIEW FRAME curr-frame. 
   END. 
ON CHOOSE OF rev-cust /* Undo pending changes to Customer record. */ 
   DO:  
      rev-chosen = TRUE. 
      /* Undo any changes to the Customer screen buffer 
         and reset the MODIFIED attributes to FALSE.      */ 
      DISABLE ALL WITH FRAME curr-frame. 
      IF AVAILABLE(Customer) 
      THEN DISPLAY Customer WITH FRAME curr-frame. 
      ENABLE ALL WITH FRAME curr-frame.  
END. 
ON ENTRY OF FRAME curr-frame 
   DO: 
      /* Upgrade from NO-LOCK to EXCLUSIVE-LOCK. */  
      FIND Customer WHERE ROWID(Customer) = curr-cust EXCLUSIVE-LOCK 
                  NO-WAIT NO-ERROR. 
      /* If we didn’t get the exclusive lock, then give a 
         message and return focus to previous frame.         */ 
      IF LOCKED(Customer) 
      THEN DO: 
          MESSAGE "Record is currently locked by another user." 
                  VIEW-AS ALERT-BOX ERROR BUTTONS OK. 
      END. 
      IF NOT AVAILABLE(Customer) 
      THEN DO: 
          APPLY "CHOOSE" TO rev-cust IN FRAME curr-frame. 
          RETURN NO-APPLY. 
      END. 
      /* Pick up any updates that may have occured. */ 
      DISPLAY Customer WITH FRAME curr-frame.  
   END. 
ENABLE ALL WITH FRAME main-frame. 
ENABLE ALL WITH FRAME curr-frame. 
APPLY "ITERATION-CHANGED" TO brow-cust. 
PAUSE 0 BEFORE-HIDE. 
main-loop: 
DO WHILE TRUE ON ENDKEY UNDO, RETURN ON ERROR UNDO, RETRY: 
   ASSIGN rev-chosen = FALSE 
          exit-chosen = FALSE. 
   WAIT-FOR CHOOSE OF exit-app OR WINDOW-CLOSE OF DEFAULT-WINDOW OR 
         CHOOSE OF com-cust, rev-cust FOCUS brow-cust IN FRAME main-frame. 
   IF AVAILABLE(Customer) 
   THEN RELEASE Customer.  
   IF exit-chosen 
   THEN LEAVE main-loop. 
   IF rev-chosen 
   THEN UNDO main-loop, RETRY main-loop.  
END. 

When you run this procedure, focus is initially in the main–frame frame. When you move focus to the curr–frame, the ON ENTRY trigger applies an EXCLUSIVE–LOCK to the customer record. Subsequently, when you either choose the com–cust button or change the iteration of the browse, a small transaction assigns your changes to the customer record. The EXCLUSIVE–LOCK on the record is then released.

Although this example appears modeless when you run it, it is really modal. When you move focus to the curr–frame frame you enter a mode in which you hold an EXCLUSIVE–LOCK on the customer record (previously, you held the record NO–LOCK). This mode remains in effect until you choose either the com–cust or rev–cust button or change the iteration of the browse. At that point, the EXCLUSIVE–LOCK is released.

NOTE: While it is possible to present a modeless update, it requires somewhat complex coding for even a simple example, as in p-txn14.p. In a more realistic example where the user has more choices of actions, this technique is more difficult and more dangerous. Using modal updates with dialog boxes is safer and is the preferred approach.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095