In this section, you try an example that uses UNDO, LEAVE on a block. To undo and leave the block that updates the OrderLine table:
1. To make this a transaction block of its own, put the END statement for the DO TRANSACTION block after the Order is updated, as you did in Controlling the size of a transaction:
...
ELSE DO: /* FIND the updated tt rec and save the changes. */
FIND bUpdateOrder WHERE bUpdateOrder.TransType = "U".
BUFFER-COPY bUpdateOrder TO Order. /* Save our changes. */
END. /* ELSE DO */
END. /* OF AVAILABLE ttOrder */
END. /* DO Transaction */
This makes the FOR EACH block that follows a separate transaction.
2. Add a new variable definition in saveOrder for an error message to return:
DEFINE VARIABLE cMessage AS CHARACTER NO-UNDO.
3. Add to the FOR EACH block that updates OrderLines the highlighted lines shown:
FOR EACH ttOline WHERE ttOline.TransType = "":
...
/* Find corresponding bUpdateOline */
FIND OrderLine WHERE OrderLine.OrderNum = ttOline.OrderNum AND
OrderLine.LineNum = ttOline.LineNum EXCLUSIVE-LOCK.
...
BUFFER-COPY bUpdateOline TO OrderLine. /* Save OrderLine changes. */
RELEASE OrderLine.
/* Re-find the db record to capture any changes made by a trigger. */
FIND OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum AND
OrderLine.LineNum = bUpdateOline.LineNum NO-LOCK.
BUFFER-COPY OrderLine TO bUpdateOline.
IF bUpdateOline.ExtendedPrice > (ttOline.ExtendedPrice * 1.2) THEN DO: cMessage = "Line " + STRING(OrderLine.LineNum) + ": Can't increase price by more than 20%.". UNDO, LEAVE. END. ...
END. /* DO FOR EACH ttOline */
This code checks to make sure that the ExtendedPrice for an OrderLine is not increased by more than 20%. If this limit is exceeded, then the current iteration of the block is undone and the block is left.
On each iteration, the FOR EACH block makes a ttOline record current. Your code uses the second buffer, bUpdateOline, to locate the updated version of that OrderLine temp-table record. It then finds the OrderLine in the database and copies the updates to it. Next it releases the database record to force its WRITE trigger to fire, which recalculates the ExtendedPrice field. Then it again finds the database record and copies it back into the bUpdateOline buffer to capture the effects of the trigger code, in particular the new ExtendedPrice value. Only now can your program logic compare this to the original value in the ttOline buffer to see if it exceeds the limit. If it does, then you store off a message, then undo and leave the FOR EACH block.
At this point, following the UNDO statement, the whole database change that you wrote out and then read back in is gone—the AVM has restored the database record to its values before the transaction began.
4. Add code so that after leaving the block, you check if you have an error message. If so, your code needs to re-find the OrderLine record with its original values, and copy it back into the bUpdateOline buffer to return to the client for display. It then returns the message as the return value for the procedure:
IF cMessage NE "" THEN DO:
FIND OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum AND
OrderLine.LineNum = bUpdateOline.LineNum NO-LOCK.
BUFFER-COPY OrderLine TO bUpdateOline.
RETURN cMessage.
END.
Why did you have to re-find the OrderLine record from the database? The UNDO released it from its buffer, so it's no longer available after the block ends. Then why did you not have to re-find the temp-table record? You defined the temp-table as NO-UNDO so it was left out of normal transaction handling. The temp-table buffer is scoped to the whole procedure, so the record it contains is still available after the block ends. If you had defined the temp-table without NO-UNDO, then the bUpdateOline record would have been released, as well as the database OrderLine record, and you would have had to re-establish it in the temp-table as well. This is an illustration of the kind of overhead you save by using NO-UNDO temp-tables, and also of the sometimes unintended consequences of having undo capability on a temp-table that doesn't require it.
The simple diagram in the following figure illustrates the scope of the transactions.
Figure 50. Example transaction scope
In the previous figure, the first transaction, which saves changes to the Order, completes at the END statement for the DO TRANSACTION block. At the end of the FOR EACH block, the transaction to save the current OrderLine ends, committing those changes to the database, releasing the OrderLine record, and then going back to the beginning of the block. Each OrderLine is saved in a separate transaction.
The UNDO statement rolls back the transaction for the current OrderLine and leaves the FOR EACH block, which skips any remaining ttOline records. But any previously committed OrderLine changes remain in the database. For example, in the following figure, the user changes the Price for Line 1 from 7.49 to 7.60, for Line 2 from 23.00 to 30.00, and for Line 3 from 13.98 to 13.50.
Figure 51. Order Updates example
The first and third changes are valid, but the second one is not. It increases the ExtendedPrice by too much. So when the user clicks Save, the user sees an error message for Line 2, as shown in the following figure.
Figure 52. Order Updates example message
But if the user presses the Fetch button to refresh all the OrderLines:
The change to Line 1 is committed, because it is in its own transaction.
The change to Line 2 is undone, because it failed the constraint.
The change to Line 3 is never even applied because the code left the block after the error on Line 2.
The following figure shows the result.
Figure 53. Order Updates example (after Fetch)
This might not be the behavior you want. On the one hand, you might want all OrderLines to be committed together or all undone if any one fails. In another case, you might want to process each OrderLine as a separate transaction, but keep going if one of them fails. Look at both of those cases.
In the first case, you want all the OrderLine updates to either succeed or fail together. If any one fails its validation, all are rolled back.
To commit all OrderLine records together or undo if any one record update fails:
1. Define the transaction scope to be greater than a single iteration of the FOR EACH block by putting a new DO TRANSACTION block around it. Then add a label for that new block to identify how much to undo in your UNDO statement:
OlineBlock: DO TRANSACTION: FOR EACH ttOline WHERE ttOline.TransType = "":
2. Change the UNDO statement to undo the entire larger transaction and to leave that outer block as well:
UNDO OlineBlock, LEAVE OlineBlock.
Remember that the default is to undo and leave the innermost block with the error property, the FOR EACH block.
3. Add another END statement to match the new DO TRANSACTION statement that begins the new block:
END. /* ELSE DO If we updated the OrderLine */
END./* DO FOR EACH ttOline */
END. /* new DO TRANSACTION block */
Note that a block label, such as OlineBlock:, does not require a matching END statement. It is simply an identifier for a place in the code and does not actually start a block of its own.
4. Make a change to the code that restores the original values to the temp-table if there's an error. Because the error might not be on the line that's current when you leave the block, you need to re-read all the OrderLines for the Order and buffer-copy their values back into the update copies of each of the temp-table records, in a FOR EACH loop:
IF cMessage NE "" THEN DO:
FOR EACH OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum NO-LOCK: FIND bUpdateOline WHERE OrderLine.LineNum = bUpdateOline.LineNum AND bUpdateOline.TransType = "U". BUFFER-COPY OrderLine TO bUpdateOline.
END.
RETURN cMessage.
END.
Now if you make changes to three OrderLines, and the second of the three is invalid, then all three changes are rolled back because they're all part of one large transaction. You see this reflected in your window.
The following figure shows a sketch of what this variation looks like.
Figure 54. Variation of transaction scope
Now look at the second case. You'd like each OrderLine committed independently of the others, and you'd like to keep going if one is invalid. In this case, you can use the NEXT option on the UNDO statement instead of LEAVE. If an error is found, the current transaction is undone and your code continues on to the next ttOline record.
To commit each OrderLine record independently and continue if any one update fails:
1. Remove the OlineBlock block label, along with the DO TRANSACTION block header and its matching END statement, from around the FOR EACH block.
2. Change the UNDO, LEAVE statement to UNDO, NEXT.
3. Since it is now possible to get errors on more than one OrderLine at a time, you should be prepared to accumulate multiple error messages in your message string.
4. Append each new message to the end of the string by using the plus sign (+) as a concatenation symbol for the character variable (cMessage = cMessage + ...).
5. Put a newline character at the end of each message, using the CHR function to append the ASCII character whose numeric value is 10 to the string:
IF bUpdateOline.ExtendedPrice > (ttOline.ExtendedPrice * 1.2) THEN DO:
cMessage = cMessage + "Line " + STRING(OrderLine.LineNum) +
": Can't increase price by more than 20%." + CHR(10). UNDO, NEXT.
END.
6. Run the window.
7. Enter a valid price for Line 1 and invalid prices for Lines 2 and 3. You see error messages for both of these:
You can also see that the valid change for Line 1 is kept because you're back to making each iteration of the FOR EACH block its own transaction.
The following figure shows a sketch of this variation.
The change to Line 1 is committed, because it is in its own transaction.
