The WHEN condition

As an option for triggers on tables, you can precede a triggered action with a WHEN clause to make the action dependent on the outcome of a test. The WHEN clause consists of the keyword WHEN followed by the condition statement given in parentheses. In the CREATE TRIGGER statement, the WHEN clause follows the keywords BEFORE, AFTER, or FOR EACH ROW and precedes the triggered-action list.

When a WHEN condition is present, if it evaluates to true, the triggered actions execute in the order in which they appear. If the WHEN condition evaluates to false or unknown, the actions in the triggered-action list do not execute. If the trigger specifies FOR EACH ROW, the condition is evaluated for each row also.

In the following trigger example, the triggered action executes only if the condition in the WHEN clause is true; that is, if the post-update unit price is greater than two times the pre-update unit price:

CREATE TRIGGER up_price
   UPDATE OF unit_price ON stock
   REFERENCING OLD AS pre NEW AS post
   FOR EACH ROW WHEN(post.unit_price > pre.unit_price * 2)
   (INSERT INTO warn_tab 
    VALUES(pre.stock_num, pre.manu_code, pre.unit_price,
           post.unit_price, CURRENT));

For more information on the WHEN condition, see the CREATE TRIGGER statement in the HCL OneDB™ Guide to SQL: Syntax.