Trigger event predicates

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 anywhere in the triggered action of a CREATE TRIGGER statement that uses a compound SQL (compiled) statement as the SQL-procedure-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;