Handlers in SQL triggers

 

A handler in an SQL trigger gives the SQL trigger the ability to recover from an error or log information about an error that has occurred while processing the SQL statements in the trigger routine body.

In the following example, there are two handlers defined: one to handle the overflow condition and a second handler to handle SQL exceptions.

CREATE TABLE ExcessInventory(Description VARCHAR(50), ItemWeight SMALLINT)


CREATE TABLE YearToDateTotals(TotalWeight SMALLINT)


CREATE TABLE FailureLog(Item VARCHAR(50), ErrorMessage VARCHAR(50), ErrorCode INT)


CREATE TRIGGER InventoryDeleteTrigger AFTER DELETE ON ExcessInventory REFERENCING OLD AS old_row FOR EACH ROW MODE DB2ROW

BEGIN
  DECLARE sqlcode INT;
  DECLARE invalid_number condition FOR '22003';
  DECLARE exit handler FOR invalid_number   INSERT INTO FailureLog VALUES(old_row.Description,
        'Overflow occurred in YearToDateTotals', sqlcode);
  DECLARE exit handler FOR sqlexception   INSERT INTO FailureLog VALUES(old_row.Description,
        'SQL Error occurred in InventoryDeleteTrigger', sqlcode);
  UPDATE YearToDateTotals SET TotalWeight=TotalWeight +
        old_row.itemWeight;

END

First, the current values for the tables are initialized.

INSERT INTO ExcessInventory VALUES('Desks',32500)

INSERT INTO ExcessInventory VALUES('Chairs',500)

INSERT INTO YearToDateTotals VALUES(0)

When the first SQL delete statement below is executed, the ItemWeight for the item "Desks" is added to the column total for TotalWeight in the table YearToDateTotals. When the second SQL delete statement is executed, an overflow occurs when the ItemWeight for the item "Chairs" is added to the column total for TotalWeight, as the column only handles values up to 32767. When the overflow occurs, the invalid_number exit handler is executed and a row is written to the FailureLog table. The sqlexception exit handler runs, for example, if the YearToDateTotals table was deleted by accident. In this example, the handlers are used to write a log so that the problem can be diagnosed at a later time.

DELETE FROM ExcessInventory WHERE Description='Desks'

DELETE FROM ExcessInventory WHERE Description='Chairs'

 

Parent topic:

SQL triggers