Receiving Results from a Procedure in a Trigger

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 

Note

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