Administrator > DB2

Making System Triggers Caller Aware

Triggers are an important database resource that can be used to implement business logic. As the data within a table changes, a trigger can be instrumented to execute (fire) before or after the data operation has completed. The trigger can even be created in a way that it replaces the data-manipulation request (INSTEAD OF). As business rules become more complex, the number and complexity of triggers usually grow as well.

In this article, I’ll show how the Retrieve Job Information (QUSRJOBI()) API can be used to improve the logic available to IBM DB2* for i trigger programs. Through the use of the QUSRJOBI() API, the trigger program can determine which application program is driving the data manipulation that caused the trigger to fire. This knowledge lets the trigger be more refined with its business rules.

Foundation Building

A trigger is a set of actions that runs automatically whenever a specified event occurs to a specified table or view. An event can be an insert, update, delete or read operation. A trigger can run either before or after the event. DB2 for i supports SQL insert, update, delete and read triggers. Trigger programs come in two flavors on DB2 for i: system trigger and SQL trigger.

System triggers are programs coded and built by the user. They can be implemented using a high-level language or the control language. The trigger is installed and removed using the Add Physical File Trigger (ADDPFTRG) and the Remove Physical File Trigger (RMVPFTRG) system commands. When the trigger is added, the Trigger Time (TRGTIME) command parameter is used to specify whether the trigger program is called before or after the data operation.

System triggers can determine the commitment-control level used by the application that caused the trigger to fire. The trigger program can use this information to set its own isolation level to match. Similarly, IBM recommends that users create the trigger program with ACTGRP(*CALLER) to allow it to run within the activation group of the invoker. By setting the isolation level and activation group to match the invoker’s environment, the system trigger will participate within the transaction and avoid unnecessary lock conflicts and commitment-control problems.

When multiple triggers have the same triggering SQL operation, the triggers are fired in the following order: First, system triggers (installed via the ADDPFTRG command) and SQL triggers built with the MODE DB2ROW clause are fired in the order in which they were created. Second, SQL triggers built with MODE DB2SQL (the default clause) are fired in the order in which they were created.

Self-referencing triggers can become inoperative if a table is copied or restored to a different library. When a trigger is inoperative, the trigger firing operation (DELETE, INSERT or UPDATE) will fail with SQLCODE set to -7048. The Display File Description (DSPFD) command can be used to review the trigger existence and state for a specific table. Inoperative triggers must be dropped and recreated, but this action can change the trigger firing order if multiple triggers exist over the same table with the same triggering SQL operation.

SQL triggers are based on the SQL language. The CREATE TRIGGER SQL statement is used to specify the trigger options and trigger body. The database manager generates ILE C code, which is used to create an ILE C module and the ILE C trigger program, within the QTEMP library. The create statement includes options to allow the trigger to be executed before, after or instead of the triggering data operation. SQL triggers have commitment-control recognition built in. They’ll recognize and match the isolation level of the code that issued the SQL statement that caused the trigger to fire. SQL trigger programs are built with ACTGRP(*CALLER), so they run within the activation group of the invoker.

Even though the database is building the program on behalf of the user for SQL triggers, the user is still required to have *USE authority to the Create Program (CRTPGM) and ADDPFTRG commands. If those authorizations aren’t in place, the CREATE TRIGGER statement will fail with SQLCODE set to -552 and SQLSTATE set to 42502. For more on message failures, see “Insider’s Tip for SQL Message Failures,” below.

Trigger Setup and Installation

Online Code Sample 1 (http://www.ibmsystemsmag.com/ibmi/23436p1.aspx) provides instructions for building a trigger. Code Sample 2 (http://www.ibmsystemsmag.com/ibmi/23437p1.aspx) provides the code for creating a sample corporate database, creating and installing the trigger program, creating the procedures to drive the trigger and issuing the SQL statements to cause it to fire.

The example trigger program in Code Sample 2 is built upon the DB2 sample corporate database. The trigger program captures the name and library of the program that caused the trigger to fire. It also captures the trigger-firing SQL statement text. These pieces of data are inserted into an ACTIVITY table along with the current timestamp, the base table name and the base library name.

The example shows how data can be inserted directly or from within two different procedures. With the help of the QUSRJOBI() API, the trigger program can distinguish the different cases. That differentiation lets the trigger program implement different business logic from within the single trigger program.

I’ll also provide sample code in case you want to create a table to demonstrate how the trigger program can use QUSRJOBI() to understand the trigger-calling environment:

DROP TABLE ACTIVITY;
CREATE TABLE ACTIVITY (TRIGGER_TIME TIMESTAMP,
TABLE_NAME CHAR(10),
TABLE_LIBRARY CHAR(10),
PROGRAM_NAME CHAR(10),
PROGRAM_LIBRARY CHAR(10),
SQL_STMT VARCHAR(3000)
ALLOCATE(256) );

Next page: >>