According to IBM's i5/OS Information Center for V5R4, a "trigger is a set of actions that run automatically when a specified change or read operation is performed on a specified database file".
Triggers can be implemented in a high-level language or SQL and have a variety of uses, including enforcing business rules or validating input data. This article explores the use of triggers to write data to other files as a means of tracing data inserts, deletes or changes. While we could use other system functions such as journaling to accomplish this, trigger programs can provide a less complicated method of auditing by selecting only specific columns or fields from the database. With a little ingenuity it's fairly easy to automate the selection of specific data for auditing and to revise those selections if the structure of the database changes.
By combining a few custom programs with native DB2 triggers we can easily set up the ability to track only database changes we want to know about.
In this example, we will use the customer file example in library QPDA with the following fields:
| Field |
Text |
Len |
Dec |
| CUST |
Customer Number |
5 |
|
| NAME |
Customer Name |
20 |
|
| ADDRESS |
Street Address |
20 |
|
| CITY |
City |
20 |
|
| STATE |
State |
2 |
|
| ZIP |
Zip Code |
5 |
0 |
| SEARCH |
Customer Number Search Code |
6 |
|
| CUTYPE |
Customer Type |
1 |
|
| ARBAL |
Accts Rec Balance |
8 |
2 |
| ORDBAL |
A/R Amt in Order File |
8 |
2 |
| LSTAMT |
Last Amount Paid in A/R |
8 |
2 |
| LSTDAT |
Last Date Paid in A/R |
8 |
2 |
| CRDLMT |
Customer Credit Limit |
8 |
2 |
| SLSYR |
Customer Sales This Year |
10 |
2 |
| SLSLYR |
Customer Sales Last Year |
10 |
2 |
In a typical production environment we would only really want to track changes to static information such as name, type or credit limit but not volatile fields like A/R balance. The first program (AUDSET10) displays the file selection screen and allows revision or deletion of existing files or adding new files to the audit process.
Click on image for larger version
Function key F6 calls the setup prompt program:
Click on image for larger version
Then select the fields you want to audit by putting a "Y" next to the field. If this is an edit of an existing audit file, "N' next to the field will remove that field from auditing.
Click on image for larger version
Based on the selection, an audit file is created with a name of @ plus the first nine characters of the original file name (in this case @QCUSDATA), and only contains the fields selected on screen AUDSET30. An RPGLE program is also created to capture the record changes, triggers are added to the audited file and an entry is made in the audit files tracking screen.
Technical details:
- Use system APIs (QUSCRTUS, QUSROBJD, QUSLFLD, QUSRTVUS, QDBRTVFD and QMHSNDPM) to display and select fields to create the audit file.
- Generate a "create table" SQL source member in QTBLSRC in the data library which includes the selected fields and record format AUDITFMT.
- Generate and compile an RPGLE source member in the selected program library source file to process the trigger actions.
- Add triggers to the data file for after insertion, after delete, and capture before and after images on changes.
Inserting a record into QCUSDATA generates an insert entry (type 1) to the audit file with the user name and time stamp. Changing the record generates a before entry (type 3) and an after entry (type 4) to the audit file so that you can compare the changed fields and know who did it and when. Deleting the record generates a before deletion entry (type 2). Notice that the newly created file @QCUSDATA has three additional fields called @@TIME, @@USER and @@DBACT which are the date/time the trigger was called, the user who called it and the action taken on the record. You can see from the records below that a record was created, changed and then deleted. The second and third records indicate a change in the customer credit limit field with before and after entries.
| ADDRESS |
CITY |
CRDLMT |
CUST |
CUTYPE |
SEARCH |
STATE |
ZIP |
@@TIME |
@@USER |
@@DBACT |
| 123 Brown Avenue |
Needham |
1000 |
1 |
1 |
TLA |
MA |
2492 |
2009-09-28-
01.36.51.427000 |
MB1PITCHER |
1 |
| 123 Brown Avenue |
Needham |
1000 |
1 |
1 |
TLA |
MA |
2492 |
2009-09-28-
01.37.09.993000 |
MB1PITCHER |
3 |
| 123 Brown Avenue |
Needham |
3000 |
1 |
1 |
TLA |
MA |
2492 |
2009-09-28-
01.37.09.993000 |
MB1PITCHER |
4 |
| 123 Brown Avenue |
Needham |
3000 |
1 |
1 |
TLA |
MA |
2492 |
2009-09-28-
01.37.30.109000 |
MB1PITCHER |
2 |
ABOUT THE AUTHOR: Steve Pitcher is the Enterprise Servers and Application Analyst for Minas Basin Pulp & Power in NS, Canada. He's been specializing in System i and Lotus Domino solutions for the last decade. His blog, ENDJOBABN covers his adventures in, and amusement with, his work on the IBM i and Lotus Domino.
