Trigger event predicates

A trigger event predicate is used in a triggered action to test the event that activated the trigger. It is only allowed in the triggered action of a CREATE TRIGGER statement.

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

A trigger event predicate can be used anywhere in the triggered action of a CREATE TRIGGER statement.

Example

The following trigger, by using trigger event predicates in the routine body, 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.

 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 NBREMP = NBREMP + 1;
      END IF;
      IF DELETING
        THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP - 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