Guru: How To Cancel A Bad SQL Update

May 15, 2017 Ted Holt

In Three Ways To Manage Unmatched Data I wrote about the use of the RAISE_ERROR function to force a SELECT statement to cancel when unmatched data is considered a fatal error. Another good use of RAISE_ERROR is to force an UPDATE statement to cancel when an invalid condition occurs.

To illustrate, imagine that you and I work in a factory. All factories have inventory. The people we serve purchase some inventory items and manufacture others. Our job is to write a program that will allow certain people to zero out the inventory balance for certain types of purchased items.

The users will enter a series of item numbers into a database table (physical file) named ItemBal3. Our program is to set the quantity on hand to zero, but only for type 3 and type 4 items. Our program contains this UPDATE statement:

update items as i
   set i.QtyOnHand = 0
 where i.type in ('3','4')
   and i.itemnumber in (select item from ItemBal3);


If the users enter the ID numbers of items that are of other types, the program ignores those items.

Let’s take it a step further. Suppose that the presence of some other type of item in ItemBal3 is an error that cannot be overlooked. In such a case, we can make the UPDATE cancel itself, like this:

update items as i                                            
   set i.QtyOnHand =                                                  
          case when i.type in ('3','4') then 0                      
               else raise_error ('97905',                           
                         'Non-purchased items cannot be zeroed') end
 where i.itemnumber in (select item from ItemBal3)

Notice that the item type is no longer tested in the WHERE clause, but in the SET. If the database manager attempts to modify a type-3 or type-4 item, the case expression returns zero, which is assigned to the QtyOnHand column.

But if the database manager attempts to modify an item of another type, the system calls the RAISE_ERROR function, which cancels the UPDATE and returns SQL state 97905 to the caller. If the program is running under commitment control, the database manager rolls back any items that were changed before the invalid item was encountered. I can illustrate with an example.

Here is the item master table:

Here is ITEMBAL3.

If the system updates the items in the order in which they are listed, the quantity on hand for A-7 and B-1 changes from 20 to zero. But when it tries to update F-3, RAISE_ERROR cancels the UPDATE statement. Under commitment control, the quantity on hand reverts to 20. Without commitment control, A-7 and B-1 have a zero balance after the canceled UPDATE.

Under commitment control, database integrity is preserved. You can find the problem, fix it, and restart the program.

Without commitment control, you don’t know what you have. Good luck.

RELATED STORY

Three Ways To Manage Unmatched Data