BEFORE and AFTER triggered actions

Triggered actions that occur before or after the trigger event execute only once. A BEFORE triggered action executes before the triggering statement, that is, before the occurrence of the trigger event. An AFTER triggered action executes after the action of the triggering statement is complete. BEFORE and AFTER triggered actions execute even if the triggering statement does not process any rows.

Among other uses, you can use BEFORE and AFTER triggered actions to determine the effect of the triggering statement. For example, before you update the quantity column in the items table, you could call the SPL routine upd_items_p1 to calculate the total quantity on order for all items in the table, as the following example shows. The procedure stores the total in a global variable called old_qty.

CREATE PROCEDURE upd_items_p1()
   DEFINE GLOBAL old_qty INT DEFAULT 0;
   LET old_qty = (SELECT SUM(quantity) FROM items);
END PROCEDURE;

After the triggering update completes, you can calculate the total again to see how much it has changed. The following SPL routine, upd_items_p2, calculates the total of quantity again and stores the result in the local variable new_qty. Then it compares new_qty to the global variable old_qty to see if the total quantity for all orders has increased by more than 50 percent. If so, the procedure uses the RAISE EXCEPTION statement to simulate an SQL error.

CREATE PROCEDURE upd_items_p2()
   DEFINE GLOBAL old_qty INT DEFAULT 0; 
   DEFINE new_qty INT;
   LET new_qty = (SELECT SUM(quantity) FROM items); 
   IF new_qty > old_qty * 1.50 THEN
      RAISE EXCEPTION -746, 0, 'Not allowed - rule violation';
   END IF
END PROCEDURE;

The following trigger calls upd_items_p1 and upd_items_p2 to prevent an extraordinary update on the quantity column of the items table:

CREATE TRIGGER up_items
   UPDATE OF quantity ON items
      BEFORE(EXECUTE PROCEDURE upd_items_p1())
      AFTER(EXECUTE PROCEDURE upd_items_p2());

If an update raises the total quantity on order for all items by more than 50 percent, the RAISE EXCEPTION statement in upd_items_p2 terminates the trigger with an error. When a trigger fails in a database that has transaction logging, the database server rolls back the changes that both the triggering statement and the triggered actions make. For more information on what happens when a trigger fails, see the CREATE TRIGGER statement in the HCL OneDB™ Guide to SQL: Syntax.