Triggers by Examples | Intelligent Enterprises of the 21st Century

Now after you know the basics of the DB2 triggers, it is the time to look at a few examples which will show you how to use DB2 triggers to enforce your business logic.

A BEFORE Trigger Example

BEFORE TRiggers are activated before the triggering SQL statement executes. By using a BEFORE trigger, you have the opportunity to supply values, validate data, and even reject the triggering SQL statement according to user-defined rules. In this section, a trigger will be created to activate before an INSERT into a table. Using similar code, you can create triggers to activate before UPDATE and DELETE operations.

In the SAMPLE database, a table called cl_sched is used to store data for class schedules. Two columns in cl_schedstarting and endingdefine when the class starts and ends, respectively.

A simple rule might be to assume that a class ends one hour after it begins if the ending time is not provided upon INSERT into this table. The trigger in Figure 9.17 will enforce this.

Figure 9.17. Example of a basic BEFORE INSERT trigger

 CREATE TRIGGER default_class_end NO CASCADE BEFORE INSERT ON cl_sched      --(1) REFERENCING NEW AS n                      --(2) FOR EACH ROW                              --(3) MODE DB2SQL WHEN (n.ending IS NULL)                   --(4)     SET n.ending = n.starting + 1 HOUR    --(5)

Figure 9.17 shows how to create a trigger called default_class_end which activates before an insert on table cl_sched on Line (1). To intercept and supply a default-ending time, the trigger needs to make reference to values provided by the triggering INSERT statement. The REFERENCING NEW AS n clause on Line (2) associates the transition values provided by the INSERT statement with the qualifier n. The ending time of the INSERT statement can then be checked and/or supplied by referring to n. ending on Lines (4) and (5).

On Line (3), FOR EACH ROW means that this trigger will activate for every row that is inserted. Therefore, if you had executed the following INSERT statement:

 INSERT INTO cl_sched (class_code, day, starting) VALUES ('DB20002', 1, '12:00'), ('DB20003', 3, '9:00')

Note

DB2 on zSeries does not support multiple-row INSERT with the previous syntax. You need to modify the previous INSERT statement to two INSERT statements, one for each row.

The trigger would be activated twice, once for each row inserted, even though only a single INSERT statement was issued.

The trigger is defined to activate only when the ending time is null using the optional WHEN clause on Line (4), which ensures that the trigger only activates if a value has not been supplied.

Finally, if the trigger is activated, the ending time is supplied automatically and is set to one hour after the starting time on Line (5).

To test this trigger, execute the following SQL statement:

 INSERT INTO cl_sched (class_code, day, starting) VALUES ('DB20001', 1, '10:00')

Now, if you select all rows from cl_sched, you'll see that the class has been automatically set to have an ending time of 11:00.

 SELECT * FROM cl_sched CLASS_CODE DAY    STARTING ENDING ---------- ------ -------- -------- DB20001         1 10:00:00 11:00:00   1 record(s) selected.

Similarly, the trigger does not execute if an ending time is supplied:

 INSERT INTO cl_sched (class_code, day, starting, ending) VALUES ('DB20002', 2, '12:00', '15:00')

Now, selecting from the cl_sched will show that the ending time remains at 15:00.

 SELECT * FROM cl_sched CLASS_CODE DAY    STARTING ENDING ---------- ------ -------- -------- DB20001         1 10:00:00 11:00:00 DB20002         2 12:00:00 15:00:00   2 record(s) selected.

An AFTER Trigger Example

As stated earlier, unlike BEFORE triggers, AFTER triggers allow you to use INSERT, UPDATE, or DELETE statements inside the trigger body. This would be useful if you wanted to transparently keep an audit trail of when certain events occurred.

To support the following example, connect to the sample database and execute the following DDL to create a table called audit:

 CREATE TABLE AUDIT (event_time TIMESTAMP, desc VARCHAR(100))

Figure 9.18 includes a trigger that can be used to keep an audit trail of salary changes with related information such as date and time of the change, as well as the person who made the change.

Figure 9.18. An example of a basic AFTER UPDATE trigger.

 CREATE TRIGGER audit_emp_sal AFTER UPDATE OF salary ON employee REFERENCING OLD AS o NEW AS n                         --(1) FOR EACH ROW MODE DB2SQL INSERT INTO audit VALUES                              --(2) (CURRENT TIMESTAMP, ' Employee ' || o.empno || ' salary changed from ' || CHAR(o.salary) || ' to ' || CHAR(n.salary) || ' by ' || USER)

Note

If the iSeries sample database is being used, you will need to drop the foreign key named red from the employee table for the example in Figure 9.18 to work.

In Figure 9.18, the trigger is able to reference to both old and new values because it is defined to activate upon table updates on Line (1). Upon any change in salary for any employee, the trigger will insert into the audit table a record of when the update occurred, what the old and new values are, and who executed the UPDATE statement on Line (2). USER is a DB2 special register that holds the connection ID of the application. Refer to Chapter 2, "Basic SQL Procedure Structure," for more information. Also, the WHEN clause in this example has been left out so that this trigger will activate unconditionally.

To test this trigger, update Theodore Spenser's salary because he seems to be underpaid relative to other managers. To see the salaries of current managers, issue the following query:

 SELECT empno, firstnme, lastname, salary FROM employee WHERE job='MANAGER' EMPNO  FIRSTNME     LASTNAME        SALARY ------ ------------ --------------- ----------- 000020 MICHAEL      THOMPSON           41250.00 000030 SALLY        KWAN               38250.00 000050 JOHN         GEYER              40175.00 000060 IRVING       STERN              32250.00 000070 EVA          PULASKI            36170.00 000090 EILEEN       HENDERSON          29750.00 000100 THEODORE     SPENSER            26150.00   7 record(s) selected.

To give Theodore a 15 percent raise, issue the following UPDATE statement:

 UPDATE employee e SET salary=salary*1.15 WHERE e.empno= '000100';

Now you can check the status of the employee table to see Theodore's new salary.

 SELECT empno, firstnme, lastname, salary FROM employee e WHERE e.empno='000100' EMPNO  FIRSTNME     LASTNAME        SALARY ------ ------------ --------------- ----------- 000100 THEODORE     SPENSER            30072.50   1 record(s) selected.

Finally, verify that the salary update has been logged in the AUDIT table:

 SELECT * FROM AUDIT EVENT_TIME                 DESC -------------------------- ---------------------------------------------------- 2002-04-21-21.26.07.665000 employee 000100 salary changed from 0026150.00 to 0030072.50  by DB2ADMIN   1 record(s) selected.

A Complex AFTER Trigger Example

In the examples presented thus far, the trigger code bodies have only contained single SQL statements. In this section, previous examples will be extended to show you how to incorporate more complex logic using the SQL PL elements you've already seen in previous chapters.

Returning to the example in Figure 9.17, with the default class time suppose that some restrictions have been added with respect to when a class can be scheduled:

A class cannot end beyond 9 PM.
A class cannot be scheduled on weekends.

A trigger could be defined to disallow the INSERT, UPDATE, or DELETE on table cl_sched that violates the aforementioned rules and returns a descriptive error to the application.

If you created the trigger from the previous example, drop it before continuing with this example. Note that an ALTER TRIGGER statement does not exist. Therefore, to modify a trigger you must drop and re-create it:

 DROP TRIGGER default_class_end

Figure 9.19 contains the trigger code to enforce the new rules.

Figure 9.19. An advanced BEFORE INSERT trigger using SQL PL (for LUW and iSeries only).

 CREATE TRIGGER validate_sched NO CASCADE BEFORE INSERT ON cl_sched REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL vs: BEGIN ATOMIC                                                 --(1)    -- supply default value for ending time if null    IF (n.ending IS NULL) THEN                                    --(2)       SET n.ending = n.starting + 1 HOUR;    END IF;    -- ensure that class does not end beyond 9PM    IF (n.ending > '21:00') THEN                                  --(3)       SIGNAL SQLSTATE '80000'          SET MESSAGE_TEXT='class ending time is beyond 9pm';     --(4)    ELSEIF (n.DAY=1 or n.DAY=7) THEN                              --(5)       SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='class cannot       be scheduled on a weekend';                                --(6)    END IF; END vs                                                           --(7)

Note

The example in Figure 9.19 does not work on zSeries due to a limitation of the statements that can be used in the trigger body. The list of supported SQL statements as well as an alternative example (shown in Figure 9.33) is provided in the zSeries considerations section.

Figure 9.33. An advanced BEFORE INSERT trigger using SQL PL in DB2 for zSeries.

 CREATE TRIGGER validate_sched_1 NO CASCADE BEFORE INSERT ON cl_sched REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL -- supply default value for ending time if null WHEN (n.ending IS NULL) BEGIN ATOMIC     SET n.ending = n.starting + 1 HOUR; END CREATE TRIGGER validate_sched_2 NO CASCADE BEFORE INSERT ON cl_sched REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL -- ensure that class does not end beyond 9pm WHEN (n.ending > '21:00') BEGIN ATOMIC     SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='class ending time is beyond 9pm'; END CREATE TRIGGER validate_sched_3 NO CASCADE BEFORE INSERT ON cl_sched REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL -- supply default value for ending time if null WHEN (n.DAY=1 or n.DAY=7) BEGIN ATOMIC SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='class cannot be scheduled on a weekend'; END

The trigger works by first supplying a default ending time, if it has not already been provided on Line (2). Then, it ensures that the ending time does not exceed 9PM on Line (3) and has not been scheduled for a weekend on Line (5).

Here are the highlights of the example in Figure 9.19:

In order to use multiple statements in the trigger body, the SQL PL statements must be wrapped within an atomic compound statement using BEGIN ATOMIC (1) and END (7).
Within the atomic compound statement, SQL PL flow control elements like IF at (2), (3), and (5) and SIGNAL at (4) and (6) can be used.
Note that the maximum length of the error message used with SIGNAL SQLSTATE is 70 characters. If you exceed this limit, the message will be truncated without warning at run time.

To test the trigger, execute the following SQL statements:

First, attempt to insert a class where the starting time is 9 PM. Because the ending time is not supplied, 10p.m. will be assumed.

 INSERT INTO CL_SCHED (class_code, day, starting)     VALUES ('DB20005', 5, '21:00')

This insert statement results in the following custom error, as desired:

 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "class ending time is beyond 9pm". SQLSTATE=80000

Next, attempt to insert a class where the day of the week is Sunday (the value of DAY starts from Sunday with a value of 1).

 INSERT INTO CL_SCHED (class_code, day, starting, ending)     VALUES ('DB20005', 1, '13:00', '15:00')

Again, the insert statement results in the following custom error, as expected:

 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "class cannot be scheduled on a weekend". SQLSTATE=80001

Finally, insert a valid value into the class schedule table (Thursday, 1 to 3 PM).

 INSERT INTO CL_SCHED (class_code, day, starting, ending)     VALUES ('DB20005', 5, '13:00', '15:00')

By selecting from the cl_sched table, you will see the row that was just inserted. (You may see another row, as shown next, if you attempted the previous example.)

 SELECT * FROM cl_sched CLASS_CODE DAY    STARTING ENDING ---------- ------ -------- -------- DB20001         1 10:00:00 11:00:00 DB20002         2 12:00:00 15:00:00 DB20005         5 13:00:00 15:00:00   3 record(s) selected.

An INSTEAD OF Trigger Example (for LUW Only)

Figure 9.20 displays a view based on table org in the sample database. It gives you the department count by divisions.

Figure 9.20. An example of an un-updatable view.

 CREATE VIEW org_by_division   (division, number_of_dept) AS   SELECT division, count(*)   FROM org   GROUP BY division

A simple SELECT query

 SELECT * FROM org_by_division

shows the content of the view as following:

 DIVISION   NUMBER_OF_DEPT ---------- -------------- Corporate               1 Eastern                 3 Midwest                 2 Western                 2   4 record(s) selected.

In DB2, if a view contains the GROUP BY clause, the view is not updatable, which means you can not issue an UPDATE statement against the view. Try the following UPDATE statement, which attempts to change the Midwest division name to Southern division:

 UPDATE org_by_division SET division='Southern' WHERE division='Midwest'

You will receive the following SQL error indicating that the operation is not supported:

 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0150N The target fullselect, view, typed table, materialized query table, or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target for which the requested operation is not permitted. SQLSTATE=42807

As discussed earlier, an INSTEAD OF trigger can be used to implement the UPDATE operation you want to perform on the view which is not updatable. Figure 9.21 is such an example.

Figure 9.21. An example of an INSTEAD OF trigger.

 CREATE TRIGGER upd_org INSTEAD OF UPDATE ON org_by_division REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC   IF (o.number_of_dept != n.number_of_dept) THEN      -- (1)     SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT =         'The number of department is not updatable.';   END IF;   UPDATE org                                          -- (2)   SET division = n.division   WHERE division = o.division; END

After the trigger is created, when an UPDATE statement is issued against the view the trigger body will be executed instead. If the query is to update the number of department, it will be rejected on Line (1). It is reasonable as you cannot update the counts without adding rows into the base table. If the query is to update the division name, the UPDATE statement on Line (2) will be executed to update all corresponding rows in the org table from the old division name to the new division name.

A simple SELECT query shows the content of the base table:

 SELECT * FROM org DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION -------- -------------- ------- ---------- -------------       10 Head Office    160     Corporate  New York       15 New England    50      Eastern    Boston       20 Mid Atlantic   10      Eastern    Washington       38 South Atlantic 30      Eastern    Atlanta       42 Great Lakes    100     Midwest    Chicago       51 Plains         140     Midwest    Dallas       66 Pacific        270     Western    San Francisco       84 Mountain       290     Western    Denver   8 record(s) selected.

To see the effect of the trigger, issue an UPDATE query that attempts to update the number of department. The customized error message defined in the trigger body is returned:

 UPDATE org_by_division SET number_of_dept=2 WHERE division='Corporate' DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "The number of department is not updatable.". SQLSTATE=80001

Issue the previously failed UPDATE query again to update the division name:

 UPDATE org_by_division SET division='Southern' WHERE division='Midwest' DB20000I The SQL command completed successfully.

The query was successful because of the INSTEAD OF trigger. All the query did was to activate the trigger. It was the body of the trigger that really went through. Issue the SELECT query again to exam the content of the org table:

 SELECT * FROM org DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION -------- -------------- ------- ---------- -------------       10 Head Office    160     Corporate  New York       15 New England    50      Eastern    Boston       20 Mid Atlantic   10      Eastern    Washington       38 South Atlantic 30      Eastern    Atlanta       42 Great Lakes    100     Southern   Chicago       51 Plains         140     Southern   Dallas       66 Pacific        270     Western    San Francisco       84 Mountain       290     Western    Denver    8 record(s) selected.

Comparing with the previous content, you may notice that all Midwest division rows are renamed into the Southern division.

The IF...ELSE... block in the trigger body in Figure 9.18 cannot be replaced by the UPDATE OF or the WHEN clause because they are not supported in INSTEAD OF triggers.

A Comparison of View Triggers and Table Triggers (LUW only)

The view trigger is a relatively new concept for DBMSs. A fundamental difference exists between the table triggers and the view triggers. Figure 9.22 is used to further illustrate the difference. It consists of a simple base table, a trivial view (which is defined the exactly the same as the base table), a simple table insert trigger, and a view insert trigger.

Figure 9.22. A comparison of a view trigger and a table trigger.

 CREATE TABLE t_airport ( airport_code char(3) not null,   airport_name char(50) ); CREATE VIEW v_airport AS   SELECT * FROM t_airport; CREATE TRIGGER insert_t_airport AFTER INSERT ON t_airport FOR EACH ROW MODE DB2SQL BEGIN ATOMIC END; CREATE TRIGGER insert_v_airport INSTEAD OF INSERT ON v_airport FOR EACH ROW MODE DB2SQL BEGIN ATOMIC END;

Both triggers are very similar. They both have an empty trigger body. There is no triggered action defined. Without the triggers, the following INSERT statements through the view or to the table generates the same results:

 INSERT INTO t_airport VALUES ('YYZ', 'TORONTO'); INSERT INTO v_airport VALUES ('YYZ', 'TORONTO');

After the triggers are defined, however, the results of the INSERT statements become different. Execute the INSERT statement on the empty table:

 INSERT INTO t_airport VALUES ('YYZ', 'TORONTO');

You will see the content of the table t_airport as the following:

 AIRPORT_CODE AIRPORT_NAME ------------ -------------------------------------------------- YYZ          TORONTO   1 record(s) selected.

This result is expected. The values in the INSERT statement are all valid. The INSERT statement completed without a problem. Then the AFTER trigger is activated. Because the trigger has no action defined, it did nothing as expected.

What if you execute the INSERT statement through a view now?

 INSERT INTO v_airport VALUES ('SFO', 'SAN FRANCISCO');

You can probably expect the same behavior. After all, the two triggers look very similar. Query the table t_airport again, and you should see the following:

 AIRPORT_CODE AIRPORT_NAME ------------ -------------------------------------------------- YYZ          TORONTO   1 record(s) selected.

Nothing changed. There is no new row. Where does the record go? Is there anything wrong? Not really. Everything worked as designed. Remember if an INSTEAD OF trigger is defined, the triggering statement will only serve the pupose of activating the trigger. It will not be executed at all. Instead, the trigger body, the triggered actions, will be executed. In this example, the triggered action is not defined, to the trigger did nothing, as designed.

A slight modification of the INSTEAD OF trigger in Figure 9.21 is needed to make it work the same away as the table trigger. The result is shown in Figure 9.23.

Figue 9.23. The modified INSTEAD OF trigger.

 CREATE TRIGGER insert_v_airport INSTEAD OF INSERT ON v_airport REFERENCING NEW AS n                         -- (1) FOR EACH ROW MODE DB2SQL BEGIN ATOMIC     INSERT INTO t_airport                    -- (2)     VALUES (n.airport_code, n.airport_name); END

In order to behave the same as the table trigger in Figure 9.22, the INSERT statement to the base table has to be explicitly implemented on Line (2), and the REFERENCING clause on Line (1) has to be included in the header.

Invoking UDFs and SQL Procedures from Triggers

Both UDFs and SQL procedures can be invoked from triggers. The ability to invoke SQL procedures is particularly useful on LUW if you need to use DB2 SQL PL features that are not yet supported in the inline SQL PL subset.

Tip

On LUW, use SQL procedures to work around inline SQL PL limitations in triggers.

An example of how triggers invoke functions and procedures is illustrated in Figures 9.24, 9.25, and 9.26.

Figure 9.24. An example of a trigger invoking a function and a procedure (for LUW and iSeries).

 CREATE TRIGGER insert_employee NO CASCADE BEFORE INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL ie: BEGIN ATOMIC   DECLARE v_valid CHAR(1) DEFAULT 'N';   IF (n.empno = '') THEN     SET n.empno = new_empno();                           -- (1)   END IF;   CALL validate_dept(n.workdept, v_valid);               -- (2)   IF (v_valid = 'N') THEN     SIGNAL SQLSTATE '80001'         SET MESSAGE_TEXT= 'Incorrect department number'; -- (3)   END IF; END ie

Figure 9.25. The supporting UDF for the trigger in Figure 9.24.

 CREATE FUNCTION new_empno ( ) RETURNS CHAR(6) LANGUAGE SQL SPECIFIC new_empno ne: BEGIN ATOMIC   DECLARE v_max_empno INT;   DECLARE v_next_empno VARCHAR(12);   SET v_max_empno =                                           -- (1)     (SELECT INT(MAX(empno)) FROM employee);   SET v_next_empno =                                          -- (2)     ( '000000' || RTRIM(CHAR(v_max_empno + 10)) );   RETURN SUBSTR(v_next_empno, LENGTH(v_next_empno)-5, 6);     -- (3) END ne

Figure 9.26. The supporting SQL procedure for the trigger in Figure 9.24.

 CREATE PROCEDURE validate_dept   ( IN p_deptno VARCHAR(3),     OUT p_valid CHAR(1) )    LANGUAGE SQL    SPECIFIC validate_dept                     -- Applies to LUW and iSeries -- WLM ENVIRONMENT <env>                      -- Applies to zSeries    READS SQL DATA vd: BEGIN   -- Declare variables   DECLARE v_deptno CHAR(3);   DECLARE SQLSTATE CHAR(5) DEFAULT '00000';   -- Procedure logic   DECLARE c_dept CURSOR FOR     SELECT deptno FROM department;   OPEN c_dept;   SET p_valid = 'N';   FETCH FROM c_dept INTO v_deptno;   w1: WHILE ( SQLSTATE = '00000' ) DO     IF (v_deptno = p_deptno) THEN       SET p_valid = 'Y';       LEAVE w1;     END IF;     FETCH FROM c_dept INTO v_deptno;   END WHILE w1;   CLOSE c_dept; END vd

Note

The examples in Figures 9.24, 9.25, and 9.26 do not work on zSeries due to some restrictions in UDFs and triggers. Refer to the zSeries considerations section for details.

Figure 9.24 shows a BEFORE INSERT TRigger that performs data validation on the input data. It creates a new employee number if the input employee number is an empty string. It validates the department number, raises an SQL error, and stops the INSERT statement if the department number is not validated. A business decision has been made to implement the logic to produce a new employee number by a function so that the function can be used by other triggers, procedures, and functions if needed. The trigger only invokes the UDF on Line (1).

Another business decision is also made to use a cursor to implement the logic of department number validation. The example only illustrates how to invoke procedures in triggers as a method to use cursors indirectly. The cursor is used in a simple example. In real life, you should only use cursors when necessary; for example, you may consider using cursors because the department table is very large and because the business logic is too complex for a query to handle. For more information, refer to Chapter 5, "Understanding and Using Cursors and Result Sets." The trigger only invokes the SQL procedure on Line (2).

The only logic implemented in the trigger is to raise an SQL error if the department number is not valid on Line (3).

The UDF in Figure 9.25 generates a new employee number by adding 10 to the largest employee number that currently exists. The employee number is defined as CHAR(6) in the employee table. However, the contents are numeric strings. Because DB2 SQL PL is a strong typed language, explicit casting is needed. The maximum employee number on Line (1) needs to be cast into an integer for the next employee number calculation. The result is cast back to characters on Line (2). The RTRIM function is used on Line (2) to remove the trailing blanks after the conversion from an integer to a character string. Concatenating leading zeros on Line (2) and taking the rightmost six characters on Line (3) formats the employee number in the proper style.

The body of the UDF can be implemented in one line of the RETURN statement. It is implemented as shown in Figure 9.25 for better readability. In your own project, it is recommended that a numeric column is used as the IDs and that a DB2 SEQUENCE object or a DB2 IDENTITY column is used for auto-incremental IDs. The implementation in Figure 9.25 illustrates using UDFs with triggers. Using maximum value to calculate the next ID may cause concurrent problems in a real-life application. For more information on the SEQUENCE object or the IDENTITY column, refer to Chapter 3, "Overview of SQL PL Language Elements."

The validate_dept procedure checks to see whether the input department number is correct. A 'Y' is returned for a department number found in the department table; otherwise, an 'N' is returned. A cursor is used in this simple example to illustrate how to use SQL procedures that implement SQL PL features that are not currently supported in triggers.

If an 'N' is returned to the trigger in Figure 9.24, the IF...ELSE block on Line (3) will raise an SQL error and pass a customized error message to the application.

The use of a UDF in this example allows the trigger to reuse the new employee number-generating code, which might be shared by many database objects. Implementing SQL procedures allows the trigger to use the cursor indirectly.

To test the trigger, use a new employee record with a valid employee number but an invalid department number as shown in the following code. The shown customized error message indicates that the procedure is invoked properly, because the business logic of department number checking is only implemented in the SQL procedure.

 INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel) VALUES('000400', 'Firstname',  'T', 'Lastname', 'ABC', 1) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "Incorrect department number". SQLSTATE=80001

Now test again with a valid department number but without an employee number. A new record is successfully created. The employee number is created from the maximum existing employee number as implemented in the UDF.

 INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel) VALUES('', 'Firstname', 'T',  'Lastname', 'A00', 1) DB20000I  The SQL command completed successfully. SELECT empno, firstnme, midinit, lastname, workdept, edlevel FROM employee EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT EDLEVEL ------ ------------ ------- --------------- -------- ------- 000010 CHRISTINE    I       HAAS            A00           18 ... 000340 JASON        R       GOUNOT          E21           16 000350 Firstname    T       Lastname        A00            1   33 record(s) selected.

For other considerations of invoking SQL procedures in UDFs and triggers, refer to the next section of this chapter.