DB2 Version 9.7 for Linux, UNIX, and Windows

Trigger event predicates


>>-+-DELETING--+-----------------------------------------------><
   +-INSERTING-+   
   '-UPDATING--'

A trigger event predicate is used in a triggered action to test the event that activated the trigger. It is only valid in the triggered action of a compiled trigger definition (SQLSTATE 42601).

DELETING: True if the trigger was activated by a delete operation. False otherwise.
INSERTING: True if the trigger was activated by an insert operation. False otherwise.
UPDATING: True if the trigger was activated by an update operation. False otherwise.

Notes

Trigger event predicates can be used only in the triggered action of a CREATE TRIGGER statement that uses a compound SQL (compiled) statement. In other contexts the keywords will not be recognized and will attempt to resolve as column or variable names.

Example

The following trigger increments the number of employees each time a new person is hired (that is, each time a new row is inserted into the EMPLOYEE table); decrements the number of employees each time an employee leaves the company; and raises an error when an update occurs that would result in a salary increase greater than ten percent of the current salary, by using trigger event predicates in its conditions:

   CREATE TRIGGER HIRED
     AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
     REFERENCING NEW AS N OLD AS O FOR EACH ROW
       BEGIN
         IF INSERTING 
         THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;
         END IF;

         IF DELETING 
         THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
         END IF;

         IF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
         THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'
         END IF;
       END;