Coordinated deletions

The usefulness of transaction logging is particularly clear when you must modify more than one table. For example, consider the problem of deleting an order from the demonstration database. In the simplest form of the problem, you must delete rows from two tables, orders and items, as the following example of shows:

EXEC SQL BEGIN WORK;
EXEC SQL DELETE FROM items
   WHERE order_num = :o_num;
if (SQLCODE >= 0)
{
   EXEC SQL DELETE FROM orders
      WHERE order_num == :o_num;

{
   if (SQLCODE >= 0)
      EXEC SQL COMMIT WORK;

{
   else
{
      printf("Error %d on DELETE", SQLCODE);
      EXEC SQL ROLLBACK WORK;
}

The logic of this program is much the same whether or not transactions are used. If they are not used, the person who sees the error message has a much more difficult set of decisions to make. Depending on when the error occurred, one of the following situations applies:

• No deletions were performed; all rows with this order number remain in the database.
• Some, but not all, item rows were deleted; an order record with only some items remains.
• All item rows were deleted, but the order row remains.
• All rows were deleted.