| TechTip: More-Flexible SQL Triggers |
|
|
|
| Tips & Techniques - SQL | ||||||||||||||||||||||||||||||||
| Written by Kent Milligan | ||||||||||||||||||||||||||||||||
| Friday, 18 July 2008 | ||||||||||||||||||||||||||||||||
|
Support MC Press - Visit Our Sponsors
Search Sponsor
Do you realize how much simpler your life can be with SQL triggers?
SQL triggers have been available on DB2 for i since V5R1. The SQL trigger support provides a fast and easy way to create triggers without the extra steps of compiling a program object. In addition, the SQL trigger syntax insulates programmers from having to navigate the trigger buffer associated with external (i.e., non-SQL) triggers.
This simplicity is demonstrated in the trigger example found in Figure 1. This SQL trigger intercepts insertions into the travel expenses table to guarantee that the employee's first and last names are always in uppercase when written to the DB2 table. You simply prefix the names of the columns with the correlation identifier (n) specified on the reference clause to access the values in the new row being inserted. This SQL logic is much simpler than writing complex code to extract and change the employee name values in the trigger buffer associated with external triggers.
CREATE TRIGGER auditSpending1 BEFORE INSERT ON expenses REFERENCING NEW AS n FOR EACH ROW MODE DB2ROW BEGIN
SET n.firstname=UPPER(n.firstname); SET n.lastname =UPPER(n.lastname);
END Figure 1: This SQL trigger example ensures that the employee's name is in uppercase when written to the DB2 table.
As this example demonstrates, you can use SQL Before triggers to change column values before DB2 writes them to the table. However, SQL Before triggers are not allowed, by default, to make changes to other tables. This behavior is dictated by the SQL standards. As an example, let's add logic to the previous trigger to write data to an audit table for large expense submissions. The enhanced trigger is shown in Figure 2.
CREATE TRIGGER auditSpending2 BEFORE INSERT ON expenses REFERENCING NEW AS n FOR EACH ROW MODE DB2ROW BEGIN
SET n.firstname=UPPER(n.firstname); SET n.lastname =UPPER(n.lastname);
IF (n.totalamount > 10000) THEN INSERT INTO travel_audit VALUES(n.empno, n.deptno, n.totalamount, n.enddate); END IF; END Figure 2: This SQL trigger example writes data to an audit table for expense submissions.
This Before Trigger will not be allowed to create since it contains a statement (i.e., INSERT) that modifies data. The DB2 for i SQL Reference contains a section detailing the data access classification for each SQL statement. SQL statements falling under the Modifies SQL Data classification include INSERT, UPDATE, DELETE, and CREATE.
SQL After Triggers don't have this restriction, so the problem could be solved by moving the audit data logic to an After Trigger. However, that would require you to code and maintain two separate Insert triggers for the expenses table.
IBM recently delivered a new QAQQINI option in V5R4 and V6R1 that eliminates the need to move this "modify" logic to After triggers. The new SQL_MODIFIES_SQL_DATA QAQQINI parameter enables you to disable this restriction for SQL triggers. Specifying *YES for the SQL_MODIFIES_SQL_DATA QAQQINI parameter will allow the trigger in Figure 2 to be successfully created on V5R4 and V6R1. This assumes the following PTFs have been applied on the system:
• V5R4: SI29678 (or Database Group PTF #15) • V6R1: SI30657 (or Database Group PTF #4)
The QAQQINI parameter only has to be specified at the time that the SQL trigger is created in order to allow SQL Before triggers to perform data modification operations.
If you haven't used a QAQQINI file before, here's an example of the setup steps needed.
1. First, create a QAQQINI file based on the master file that IBM provides in the QSYS library. The Create Duplicate Object (CRTDUPOBJ) command is used to make sure that triggers are copied from the master file.
CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(MYLIB)
2. After the QAQQINI file is created, you need to populate the file with the new SQL_MODIFIES_SQL_DATA parameter. Any interface can be used to add a new row into this file, but SQL is probably the simplest.
INSERT INTO mylib/qaqqini(qqparm, qqval) VALUES('SQL_MODIFIES_SQL_DATA', '*YES')
3. The final step is to activate this new QAQQINI file for the job that will be creating the SQL triggers. If the QAQQINI file is created in the QUSRSYS library, activating the file is unnecessary because all jobs on the system look for a QAQQINI file in QUSRSYS. Assuming that the file has been created in a different library, the following Change Query Attribute (CHGQRYA) command will activate the QAQQINI file for the specified job.
CHGQRYA QRYOPTLIB(mylib)
More information on QAQQINI files and SQL triggers can be found in the IBM Systems InfoCenter.
SQL triggers can be a powerful programming tool. The new PTFs provide even more flexibility when using SQL Before triggers to enhance applications.
|
||||||||||||||||||||||||||||||||
|
Last Updated ( Saturday, 19 July 2008 ) |
||||||||||||||||||||||||||||||||
