In addition to being able to receive the results of a procedure in another procedure, you can also receive the output in a trigger. This greatly reduces the complexity and maintenance of your triggers by encapsulating code that is repeated in a stored procedure. Triggers are discussed in detail in the next chapter. Figure 8.19 shows a few simple examples are presented to illustrate how a trigger can be rewritten to use a stored procedure.
Figure 8.19. An example of a trigger to remove managers who no longer have any direct reports.
CREATE TRIGGER removeFreeManager AFTER DELETE ON department REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE staffCount INT DEFAULT 0; SET staffCount = (SELECT COUNT(*) --(1) FROM department WHERE mgrno = old_row.mgrno AND location != 'Head Quarters'); IF staffCount < 1 THEN --(2) DELETE FROM employee WHERE empno = old_row.mgrno; END IF; END
On iSeries, for the example in Figure 8.19 to work, you will need to first drop the ROD foreign key on the DEPARTMENT table:
ALTER TABLE DEPARTMENT DROP FOREIGN KEY ROD;
On zSeries, the trigger in Figure 8.19 cannot be created because the IF statement is not supported in the trigger body (this explained indepth later in the chapter).
The example in Figure 8.19 is a trigger on the department table that removes any manager who no longer has any staff to manage. The logic in section (1) could be much more complex and may be repeated in other triggers. It would be better to move the logic to a stored procedure that could be used by other triggers or procedures. The stored procedure in Figure 8.20 contains the procedure logic.
Figure 8.20. An example of a stored procedure to count the number of staff under a particular manager.
CREATE PROCEDURE count_staff (IN p_mgrNumber char(6), OUT p_staffCount INT) LANGUAGE SQL SPECIFIC count_staff -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries cs: BEGIN SELECT COUNT(*) into p_staffCount FROM department WHERE mgrno = p_mgrNumber AND location != 'Head Quarter'; END cs
Figure 8.21. An example of a trigger that calls a stored procedure.
CREATE TRIGGER removeFreeManager2 AFTER DELETE ON department REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE staffCount INT DEFAULT 0; -- DECLARE tmp_mgrno CHAR(6); --(1) applies to iSeries -- SET tmp_mgrno = old_row.mgrno; --(2) applies to iSeries -- CALL count_staff(tmp_mgrno, staffCount); --(3) use this CALL for iSeries CALL count_staff(old_row.mgrno, staffCount); IF staffCount < 1 THEN DELETE FROM employee WHERE empno = old_row.mgrno; END IF; END
The CALL statement of the trigger shown in Figure 8.21 uses a transition variable to pass in a parameter. On iSeries, a parameter being passed in a CALL statement cannot be a transition variable, hence one is declared.
A variable tmp_mgrno is declared on Line (1), initialized on Line (2) and then used in the CALL statement on Line (3).
In DB2 for zSeries as in DB2 for LUW, you are allowed to call a stored procedure from a trigger. A DB2 for zSeries trigger, however, has several limitations, such as the fact that the IF statement cannot be coded in the trigger action. These limitations are explained in more detail in Chapter 9, "User-Defined Functions and Triggers."
The sample in Figure 8.21 for the trigger removeFreeManager2 cannot be created as-is in DB2 for zSeries because the trigger contains a DECLARE and an IF statement. Rewriting the trigger so that it calls a stored procedure that includes all the logic including the IF statement should provide a partial workaround. Figure 8.20 can be rewritten as shown in Figure 8.22 and Figure 8.21 can be rewritten as shown in Figure 8.23.
Figure 8.22. Rewriting the stored procedure count_staff for DB2 for zSeries.
CREATE PROCEDURE count_staff (IN p_mgrNumber char(6)) LANGUAGE SQL WLM ENVIRONMENT <env> cs: BEGIN DECLARE v_staffcount INT DEFAULT 0; SELECT COUNT(*) INTO v_staffCount FROM department WHERE mgrno = p_mgrNumber AND location != 'Head Quarter'; IF v_staffCount < 1 THEN -- (1) DELETE FROM employee WHERE empno = p_mgrNumber; END IF; END cs
On Line (1), you can see how the IF statement logic has been moved from the trigger to the stored procedure.
Figure 8.23. Rewriting the trigger removeFreeManager for DB2 for zSeries.
CREATE TRIGGER removeFreeManager2 AFTER DELETE ON department REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL COUNT_STAFF(old_row.mgrno); END