Example 1: Ensure that the salary column is never greater than 50000. If the new value is greater than 50000, set it to 50000.
CREATE TRIGGER LIMIT_SALARY BEFORE INSERT ON EMPLOYEE REFERENCING NEW AS NEW_VAR FOR EACH ROW MODE DB2SQL WHEN (NEW_VAR.SALARY > 50000) BEGIN ATOMIC SET NEW_VAR.SALARY = 50000; END
Example 2: When the job title is updated, increase the salary based on the new job title. Assign the years in the position to 0.
CREATE TRIGGER SET_SALARY BEFORE UPDATE OF JOB ON STAFF REFERENCING OLD AS OLD_VAR NEW AS NEW_VAR FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (NEW_VAR.SALARY, NEW_VAR.YEARS) = (OLD_VAR.SALARY * CASE NEW_VAR.JOB WHEN 'Sales' THEN 1.1 WHEN 'Mgr' THEN 1.05 ELSE 1 END ,0); END
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.