Example 1: Create two triggers that track the number of employees that a company manages. The triggering table is the EMPLOYEE table, and the triggers increment and decrement a column with the total number of employees in the COMPANY_STATS table. The COMPANY_STATS table has the following properties:
CREATE TABLE COMPANY_STATS (NBEMP INTEGER, NBPRODUCT INTEGER, REVENUE DECIMAL(15,0))
This example uses row triggers to maintain summary data in another table.
Create the first trigger, NEW_HIRE, so that it 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, increase the value of column NBEMP in table COMPANY_STATS by 1.
CREATE TRIGGER NEW_HIRE AFTER INSERT ON EMPLOYEE FOR EACH ROW MODE DB2SQL UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1
Create the second trigger, FORM_EMP, so that it decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE, decrease the value of column NBEMP in table COMPANY_STATS by 1.
CREATE TRIGGER FORM_EMP AFTER DELETE ON EMPLOYEE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1; END
Example 2: Create a trigger, REORDER, that invokes user-defined function ISSUE_SHIP_REQUEST to issue a shipping request whenever a parts record is updated and the on-hand quantity for the affected part is less than 10% of its maximum stocked quantity. User-defined function ISSUE_SHIP_REQUEST orders a quantity of the part that is equal to the part's maximum stocked quantity minus its on-hand quantity. The function eliminates any duplicate requests to order the same PARTNO and sends the unique order to the appropriate supplier.
This example also shows how to define the trigger as a statement trigger instead of a row trigger. For each row in the transition table that evaluates to true for the WHERE clause, a shipping request is issued for the part.
CREATE TRIGGER REORDER AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS REFERENCING NEW_TABLE AS NTABLE FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO) FROM NTABLE WHERE ON_HAND < 0.10 * MAX_STOCKED; END
Example 3: Assume that table EMPLOYEE contains column SALARY. Create a trigger, SAL_ADJ, that prevents an update to an employee's salary that exceeds 20% and signals such an error. Have the error that is returned with an SQLSTATE of 75001 and a description. This example shows that the SIGNAL SQLSTATE statement is useful for restricting changes that violate business rules.
CREATE TRIGGER SAL_ADJ AFTER UPDATE OF SALARY ON EMPLOYEE REFERENCING OLD AS OLD_EMP NEW AS NEW_EMP FOR EACH ROW MODE DB2SQL WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY *1.20)) BEGIN ATOMIC SIGNAL SQLSTATE '75001'('Invalid Salary Increase - Exceeds 20%'); END
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.