I am a big fan of DB2 Triggers. I've used them for years. Triggers are a great way to verify data, capture any file modification (add, delete, change), or start other processes based on a change in a database without retooling existing applications. The typical steps for an RPG solution with triggers are

1. Develop the RPG trigger program. You must know the database structure and record sizes to get offsets so you can capture the before and after elements of the record.
2. Compile the RPG program.
3. Use the ADDPFTRG (Add Physical File Trigger) command to add the RPG program as a trigger. When using ADDPFTRG, you have several parameters that specify when to fire the trigger.

How would you like to take three steps down to one? Well, you can with SQL triggers. This article introduces you to DB2 file triggers using SQL, which is available with V5R1 and later versions of the operating system.

If you're new to triggers, you may want to read the article "Uncovering V5R1 Triggers" first for background information; doing so will make this article easier to understand. Also, to catch up on SPL error handling, see "Inside V5R2 SPL Error Handling: Part 1: Fundamentals" and "Inside V5R2 SPL Error Handling: Part 2: Coding a Bulletproof Stored Procedure".

An Example

The best way to show you how to create an SQL trigger is with an example where you can follow along without any worry of trampling on your production files. I'll use the readily available table (i.e., database file) QIWS/ QCUSTCDT. If you have iSeries Access on your system, you should have this table. Figure 1 shows the structure.

I want a trigger that is fired only when a change is made to the credit limit column (i.e., field) CDTLMT or the credit due column CDTDUE. This is a column-sensitive trigger, as I want to do something only when a column within the table is updated. I want the SQL trigger to look at two conditions:

1. If the credit due field is changed to be greater than $500.00, then I want an exception row to be written out to a table that I can query.
2. If a non-zero credit limit is changed by 10 percent or more, then I also want an exception row to be written out to a table that I can query.

I'll write out the exception entries to another table, CREDITCHG. Figure 2 shows the table's columns. (You can create CREDITCHG with DDS or SQL.)

Figure 3 contains the SQL statement to add the trigger to the table QIWS/QCUSTCDT. The body of the trigger (following the BEGIN statement) conditionally inserts a row (or rows) into the CREDITCHG table. Let's go through the statement carefully, and I'll explain options I used to create this SQL trigger.

The line at A shows the name of the trigger, VERIFYCHG, and where the trigger object will be stored, MYLIB.

The line at B indicates when to fire the trigger: after an UPDATE operation that references either the CDTDUE or the CDTLMT column. When no columns are specified, the trigger is fired every time the row is updated.

The lines at C specify how to reference the before and after values of the columns: Using the NEW ROW AS and the OLD ROW AS clauses, the trigger can access the before-update (old) and after-update (new) values for all columns.

The line at D indicates the granularity of the trigger: The FOR EACH ROW specifies that the trigger is called once for each updated row. For a multirow SQL UPDATE statement, MODE specifies when to take the trigger actions. MODE DB2ROW specifies that the trigger will be called immediately after each row is updated, rather than after the last row has been updated.

The lines at E indicate the conditions that must be satisfied for the trigger action to occur: In this example, either the credit due must have been changed to more than $500 or a non-zero credit limit value must have been changed. Using old and new values in a WHEN clause allows you to execute the trigger actions for a limited set of cases. Note how the column values are referenced. For example, NEWROW.CDTDUE is the new value of the column after the update.

At F, the BEGIN statement designates the beginning of the trigger's main block of executable code.

As with other programming languages, you can define variables. At G, TriggerRsn is a character-type variable with a length of 6, and its default value is Null. The variable will be used to store text identifying the reason for the exception row. The other two variables are numeric variables that hold the old and new values if an exception condition occurs.

Always make sure you have some sort of error handling in your SQL procedures. At H, I've set an exit handler for the entire procedure so that if something bad happens in the procedure, the value 01U91 will be returned as the SQLSTATE setting. Triggers can't return a result set or result variable, so setting the SQLSTATE is the best alert for a trigger.

I'm using the same trigger for two conditions, so at I in Figure 3, I test for each of the two cases. If the credit due value is greater than 500, I set the exception text in TriggerRsn and store the values I'll insert in my exception table later.

Then, at J, I handle the changes to CDTLMT of 10 percent or more.

I can now check (at K) to see whether either condition occurred by testing the value of TriggerRsn. If it isn't Null, I use the saved values to INSERT a row in the CREDITCHG table.

At L, The END statement indicates the end of the procedure.

All done! The SQL source code in Figure 3 is all we need to add the trigger to the table and execute our trigger logic.

Creating the Source

The SQL procedure source can be created four different ways:

1. Use Database options under iSeries Navigator. Under the database area of iSeries Navigator, you can select the Add SQL Triggers tab from the Properties dialog, and iSeries Nav will walk you through the creation of the SQL trigger. This is a good approach if you aren't used to SQL, as iSeries Navigator guides you through each step in defining the trigger conditions and execution statement.
2. Use the Run SQL scripts under iSeries Navigator. You can copy and paste the above statement into the Run SQL scripts in iSeries Navigator and have the statement executed. The advantage to using this tool under iSeries Navigator is that you can save the script to the IFS or to your PC hard drive or have multiple SQL statements in one PC-based source file.
3. Use Interactive SQL (ISQL) from a green-screen session. I like to use ISQL to test SQL statements. Though the statement above can be processed in ISQL, I wouldn't use ISQL for any really long statements that I might want to tweak or use again and again.
4. Use the RUNSQLSTM (Run SQL Statement) command with a source member. This is the approach that I used. I still do most of my source editing in PDM/SEU, so this is the most comfortable editing process for me. After I created the source member, the following command processed the source member:

RUNSQLSTM SRCFILE(MYLIB/QSQLSRC) 
  SRCMBR(VERIFYCHG) NAMING(*SQL)

If the statement is correct, the SQL procedure is created. You'll find three spooled files, and if you are curious, you can look at the C source code that the process creates to build the C program. In our example, you'll find an object MYLIB/VERIFYCHG, type CLE. If you have to debug the procedure, you'll find yourself viewing C code.

To test to see whether the trigger code works, you can use SQL statements. Figure 4a shows two SQL statements, and Figure 4b shows the results in the mylib.creditchg table.

Learn More

To learn more about SQL procedures and triggers, see the IBM Redbook Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries (SG24-6503) at ibm.com/redbooks.

Fire those triggers and enjoy!

Jef Sutherland is an iSeries NEWS technical editor and the vice president of IS for Kampgrounds of America, Inc., in Billings, Montana. He has worked with RPG since 1987 and with various Windows development environments for more GPFs and reboots than he cares to count. Jef is the co-author, with Bryan Meyers, of the book VisualAge for RPG by Example.