Db2 12 for z/OS SQL Enhancements: Advanced Triggers

In this, the fourth of a set of four entries covering SQL enhancements introduced with Db2 12 for z/OS, I will describe new trigger capabilities delivered by way of what are called "advanced triggers" (the kind of trigger that you could define prior to Db2 12 -- and which you can still create in a Db2 12 system -- is now referred to as a "basic trigger"). Before getting to the details of advanced trigger functionality, I want to emphasize that there are considerably more than four SQL-related enhancements delivered through Db2 12 -- I've just selected my four favorite of these enhancements for highlighting in this blog (the previous three entries in this series covered piece-wise DELETE, result set pagination, and the much-improved MERGE). A good source of information on the other SQL enhancements provided by Db2 12 is the "Application enablement" section of the "What's new" part of the Db2 12 Knowledge Center on the Web.

Note that advanced trigger functionality is available in a Db2 12 system when the activated function level is V12R1M500 or above.

OK, advanced triggers: the most important thing to know about this new kind of trigger is that it can contain (in the CREATE TRIGGER statement) a compound SQL statement. Basically, that means that you can define a trigger using SQL PL (aka SQL procedure language -- the same language that enables the creation of native SQL procedures and "native" SQL user-defined functions).

Before getting into the implications of SQL PL in the body of a trigger, I want to do a little level-setting. A trigger, for those who don't know, is a mechanism by which a data-changing action (INSERT, UPDATE, or DELETE) targeting one table can "trigger" the automatic execution of some other SQL action. A trigger can be "fired" before or after the "triggering" SQL statement has been executed. A very simple example: an AFTER UPDATE trigger can cause an UPDATE that changes column C1 of table T1 to drive an INSERT of some information into table C2.

Suppose you want the action taken when a trigger gets fired to be somewhat involved versus really simple. In that case, prior to Db2 12 it was often necessary to have the trigger call a stored procedure. That can be kind of clunky from a coding perspective. With the ability to code SQL PL in the body of an advanced trigger, you can drive a fairly sophisticated action when a triggering SQL statement executes, without having to put a stored procedure call in the trigger body (in essence, you can use SQL PL to put the equivalent of a native SQL procedure in an advanced trigger).

An example of an advanced trigger appears below, followed by some color-coded comments (this BEFORE INSERT trigger examines start and end times for classes in records to be inserted into a Db2 table, sets the end time to one hour after the start if the end time value is NULL, and returns an error if the class end time is after 9 PM):

CREATE TRIGGER MYTRIG01
BEFORE INSERT ON MYTAB
REFERENCING NEW AS N
FOR EACH ROW
ALLOW DEBUG MODE
QUALIFIER ADMF001
WHEN(N.ending IS NULL OR n.ending > '21:00')
L1: BEGIN ATOMIC
 IF (N.ending IS NULL) THEN
    SET N.ending = N.starting + 1 HOUR;
 END IF;
 IF (N.ending > '21:00') THEN
    SIGNAL SQLSTATE '80000'
    SET MESSAGE_TEXT = 'Class ending time is beyond 9 pm';
 END IF;
 SET GLOBAL_VAR = NEW.C1;
END L1#

Things to note about this advanced trigger:

• You have some new options -- Because an advanced trigger can include SQL PL statements, you can debug it, just as you can debug a native SQL procedure or a compiled SQL scalar function (Data Studio is particularly handy for debugging SQL PL routines). Another new option for advanced triggers: you can provide a high-level qualifier to be used with unqualified objects referenced in the body of the trigger.
• You can include SQL control statements (i.e., logic flow control statements) in the body of the trigger -- IF (shown in the example) is one such statement. Among the others are ITERATE, LOOP, REPEAT, and WHILE. These SQL PL statements enable the coding of a trigger that has pretty sophisticated functionality.
• There are new possibilities for the SET statement -- With an advanced trigger, SET is not restricted to transition variables -- it can also be used with global variables and SQL variables (the latter term refers to variables declared in the body of the trigger).

And, there's something that's notable by its absence in the example CREATE TRIGGER statement -- namely, the phrase MODE DB2SQL. It is, in fact, the absence of MODE DB2SQL in a CREATE TRIGGER statement that indicates that the trigger will be an advanced trigger, as opposed to a basic trigger.

Besides providing advanced functionality versus basic triggers, advanced triggers eliminate what had been a really vexing problem encountered by many users of (what are now called basic) triggers -- a problem best illustrated by example. Suppose you create three basic triggers on a table, all of which are "fired" when a particular type of statement targets the table (e.g., an UPDATE of a certain column in the table), and all of which "fire" in the same relative time period with regard to the execution of a triggering SQL statement (e.g., all three are AFTER triggers). In that case, the order in which the triggers will fire is determined by the order in which they were created: if trigger A was created first, then trigger B and then trigger C, they will fire in that order (A then B then C) upon the execution of a triggering SQL statement. Let's say that this A-B-C trigger firing sequence is important to you. Now, suppose that that trigger A has to be modified. Only way to get that done with a basic trigger is DROP and re-CREATE. But wait! That re-CREATE will make trigger A the last of the three triggers in this example to be created, resulting in an undesirable firing order of B then C then A when a triggering SQL statement is executed. How do you change trigger A and preserve the desired A-B-C firing order? Here's how: you DROP all three triggers, then re-CREATE all three in A-B-C order. What a hassle!

Along comes advanced trigger functionality, and this problem is solved -- and not only solved, but fixed by your choice of three options. All three of these hassle-free trigger modification options are made possible by the fact that advanced triggers, like native SQL procedures and compiled SQL scalar functions, have versions. So, back to the example of the preceding paragraph: you have triggers A, B, and C, and you want to change A while maintaining the A-B-C firing sequence. If they are now advanced triggers, no problem! Here are your three -- count 'em: three -- options for changing advanced trigger A without messing up the firing sequence of the triggers (I'll assume that the current version of trigger A is V2, and I'll highlight syntax that is new with Db2 12 in red):

• CREATE OR REPLACE TRIGGER A VERSION V2…
• ALTER TRIGGER A REPLACE VERSION V2…
• ALTER TRIGGER A ADD VERSION V3… followed by ALTER TRIGGER A ACTIVATE VERSION V3

Choose any of the above options, and you successfully modify trigger A while maintaining the A-B-C firing sequence of the three triggers, without having to DROP and re-CREATE triggers B and C. And the crowd goes wild!

Well, there you have it: another incentive to get to Db2 12 for z/OS (and to activate function level V12R1M500 or later). Enjoy those Db2 12 SQL enhancements, folks!