Triggers

An active trigger is a set of actions that will be executed when a defined event occurs. Such an event can be any of the the following SQL statements:

• INSERT

• UPDATE

• DELETE

A trigger is defined for a specific table. Once defined, a trigger is automatically active. A table can have multiple triggers defined for it; if multiple triggers are defined for a given table, the order of trigger activation is based on the trigger-creation timestamp, or the order in which the triggers were created.

Trigger definitions are stored in the DB2 catalog tables. The SYSIBM.SYSTRIGGERS table has one row for each trigger, and the TEXT column contains the full text of CREATE TRIGGER. The SYSIBM.SYSPACKAGE table has one row for each trigger package, and the TYPE column is set to T to indicate a trigger package.

Trigger Use

A trigger can be used for

• Data validation to ensure that a new data value is within the proper range. This use is similar to table-check constraints but is a more flexible data-validation mechanism.

• Data conditioning, or using triggers that fire before data-record modification. This allows the new data value to be modified or conditioned to a predefined value.

• Data integrity to ensure that cross-table dependencies are maintained. Useful when a child table can have multiple parent tables.

• Data propagation, with which audit, history, and summary tables can be updated based on the values placed in base tables.

The triggered action could involve updating data records in related tables. This is similar to referential integrity but is a more flexible alternative.

Triggers can also be used to enforce business rules, create new column values, edit column values, validate all input data, or maintain summary tables or cross-reference tables. Triggers provide for enhanced enterprise and business functionality, faster application development, and global enforcement of business rules.

In short, a trigger is a way of getting control to perform an action whenever a table's data is modified. A single trigger invoked by an update on a financial table could invoke a UDF and/or call a stored procedure to invoke another external action, triggering an e-mail to a pager to notify the DBA of a serious condition. Farfetched? No, it is already being done.

Triggers can cause other triggers to be invoked and, through the SQL, can call stored procedures, which could issue SQL updates that invoke other triggers. This allows great flexibility.

The cascading of triggers, stored procedures, and UDFs has an execution-time nesting depth of 16 to prevent endless cascading.The big performance concern is that, if the seventeenth level is reached, an SQLCODE of 724 is set, but all 16 levels are backed out. That could be a significant problem and not something you want to see. The real issue here is processes that are executed outside the control of DB2, as they would not be backed out, and it might be very difficult to determine what was changed. There are limitations in the calling sequences; for example, stored procedures managed by the WLM cannot call stored procedures managed by DB2.

Trigger Activation

A trigger can be defined to firebe activatedin one of two ways.

1. A before trigger will fire for each row in the set of affected rows before the triggering SQL statement executes. Therefore, the trigger body is seeing the new data values prior to its being inserted or updated into the table.

2. An after trigger will fire for each row in the set of affected rows or after the statement has successfully completed, depending on the defined granularity. Therefore, the trigger body is seeing the table as being in a consistent state, namely, that all transactions have been completed.

Triggers can fire other triggersor the same triggeror other constraints. These are known as cascading triggers. Only an after trigger can update other data causing this cascade.

During the execution of a trigger, the new and old data values can be accessible to the trigger, depending on the nature of the trigger: before or after. By using triggers, you can

• Reduce the amount of application development and make development faster. Because triggers are stored in DB2 and are processed by DB2, you do not need to code the triggers or their actions into your applications.

• Provide a global environment for your business rules. The triggers have to be defined only once and then are stored in the database, so they are available to all applications executing against the database.

• Reduce the maintenance of your applications. As the trigger is handled by DB2 and is stored in the database itself, any changes to the trigger owing to changes in your environment have to occur in only one, not multiple, applications.

Triggers can contain only SQL, but through SQL, stored procedures and UDFs can be invoked. Because stored procedures and UDFs are user-written code, almost any activity can be performed from a triggered event. The action causing the trigger may need a message sent to a special place via e-mail. The trigger might be a before trigger written to handle complex referential integrity checks, which could involve checking whether data exists in another non-DB2 storage container. Through the use of stored procedures and UDFs, the power of a trigger is almost unlimited.

Creating Triggers

Triggers are defined using the CREATE TRIGGER statement, which contains many options. The primary options are whether it is a before trigger or an after trigger, whether it is a row trigger or a statement trigger, and the language of the trigger, which is only SQL, but that will probably change in the future. The phrase MODE DB2SQL is the execution mode of the trigger. This phrase is required for each trigger to ensure that an existing application will not be negatively impacted if alternative execution modes for triggers are added to DB2 in the future. You can have up to 12 types of triggers on a single table. See Figure 15-1.

Figure 15-1. Trigger types

NOTE

Triggers are invoked in the order they were created. A timestamp is recorded when the trigger is created and recreated. A DROP and (re)CREATE of a trigger can completely mess up your process by changing the order in which triggers are executed. Be careful!

When triggers are added, the rows that are in violation of a newly added trigger will not be rejected. When a trigger is added to a table that has existing rows, triggered actions will not be activated. If the trigger is designed to enforce some type of integrity constraint on the data rows in the table, those constraints may not be enforced by rules defined in the trigger, or held true, for the rows that existed in the table before the trigger was added.

If an update trigger without an explicit column list is created, packages with an update usage on the target table are invalidated. If an update trigger with a column list is created, packages with update usage on the target table are invalidated only if the package also has an update usage on at least one column in the column-name list of the CREATE TRIGGER statement. If an insert trigger is created, packages that have an insert usage on the target table are invalidated. If a delete trigger is created, packages that have a delete usage on the target table are invalidated.

A lot of functionality can be used within a trigger. For example, a CASE expression can be used in a trigger but needs to be nested inside a VALUES statement, as shown here:

 BEGIN ATOMIC    VALUES CASE       WHEN condition          THEN something       WHEN other condition          THEN something else   END END; 

The best way to understand the use of triggers is to see some in action. The DB2CERT database contains many relationships that can be maintained using triggers.

After Trigger

In the following example, a trigger is defined to set the value of the PASS_FAIL column for each of the tests taken by a candidate. (Note that we add this column for this scenario.) The trigger has been given the name PassFail, which has no relationship with the column called PASS_FAIL. Once the trigger has been created, it is active.

The PassFail trigger is an AFTER, INSERT, and FOR EACH ROW trigger. Every time a row is inserted into the test_taken table, this trigger will fire. The trigger-body section will perform an UPDATE statement to set the value of the PASS_FAIL column for the newly inserted row. The column is populated with either the value P, representing a passing grade, or the value F, representing a failing grade.

NOTE

Remember that an after trigger defined against one table can modify other tables in the trigger body.

 CREATE TRIGGER PASSFAIL    AFTER INSERT ON db2cert.test_taken   REFERENCING NEW AS n   FOR EACH ROW MODE DB2SQL UPDATE db2cert.test_taken   SET PASS_FAIL =     CASE       WHEN n.score >=         (SELECT cut_score FROM db2cert.test          WHERE number = n.number)       THEN'P'       WHEN n.score <         (SELECT cut_score FROM db2cert.test          WHERE number = n.number)          THEN'F'          END    WHERE n.cid = cid      AND n.tcid = tcid      AND n.number = number      AND N.DATE_TAKEN = DATE_TAKEN 

Before Trigger

A before trigger will be activated before the trigger operation has completed. The triggering operation can be on an INSERT, UPDATE, or DELETE statement. This type of trigger is very useful for three purposes:

1. To condition data

2. To provide default values

3. To enforce data-value constraints dynamically

The three before-trigger examples shown are used in the DB2 Certification application. All three of these triggers have been implemented to avoid seat conflicts for test candidates. The triggers will fire during an insert of each new candidate for a test.

 ** Example 1 ** CREATE TRIGGER pre9 NO CASCADE BEFORE    INSERT ON DB2CERT.TEST_TAKEN    REFERENCING NEW AS n    FOR EACH ROW MODE DB2SQL WHEN (N.START_TIME <'09:00:00')    SIGNAL SQLSTATE 70003'    ('Cannot assign seat before 09:00:00!') ** Example 2 ** CREATE TRIGGER AFT5 NO CASCADE BEFORE    INSERT ON DB2CERT.TEST_TAKEN    REFERENCING NEW AS N    FOR EACH ROW MODE DB2SQL WHEN (N.START_TIME +    (SELECT SMALLINT(LENGTH) FROM DB2CERT.TEST    WHERE NUMBER = N.NUMBER) MINUTES       >'17:00:00')    SIGNAL SQLSTATE'70004'    ('Cannot assign seat after 17:00:00!') ** Example 3 ** CREATE TRIGGER START NO CASCADE BEFORE    INSERT ON DB2CERT.TEST_TAKEN    REFERENCING NEW AS N    FOR EACH ROW MODE DB2SQL WHEN (     EXISTS (SELECT CID FROM DB2CERT.TEST_TAKEN    WHERE SEAT_NO = N.SEAT_NO     AND TCID = N.TCID     AND DATE_TAKEN = N.DATE_TAKEN     AND N.START_TIME BETWEEN       START_TIME AND FINISH_TIME))    SIGNAL SQLSTATE '70001' ('Start Time Conflict!') 

If the conditions are encountered, an SQL error will be flagged, using the SQL function called SIGNAL. A different SQLSTATE value will be provided when the triggered conditions are encountered.

The pre9 TRigger is used to ensure that a test candidate is not scheduled to take a test before 9:00 A.M. The aft5 trigger is used to ensure that a test candidate is not scheduled to take a test after 5:00 P.M. The start trigger is used to avoid conflicts during a testing day.

Row and Statement Triggers

In order to understand the concept of trigger granularity, you need to understand the rows affected by the triggering operations. The set of affected rows contains all rows that are deleted, inserted, or updated by the triggering operations. The keyword FOR EACH ROW is used to activate the trigger as many times as the number of rows in the set of affected rows. The previous example shows a row trigger. The keyword FOR EACH STATEMENT is used to activate the trigger once for the triggering operation.

Transition Variables and Tables

Transition Variables

Transition variables allow row triggers to access columns of affected row data in order to see the row data as it existed both before and after the triggering operation. These variables are implemented by a REFERENCING clause in the definition:

 REFERENCING OLD AS OLD_ACCOUNTS             NEW AS NEW_ACCOUNTS 

The following example uses transition variables to prevent an update from occurring:

 CREATE TRIGGER TR1 NO CASCADE BEFORE    UPDATE ON EMP    REFERENCING NEW AS T1    FOR EACH ROW MODE DB2SQL WHEN (     EXISTS (SELECT 1     FROM DEPT B, EMP C    WHERE B.DEPTNO=T1.WORKDEPT    AND B.MGRNO=C.EMPNO    AND C.SALARY <= T1.SALARY) SIGNAL SQLSTATE '70001' ('Salary too big!') 

Here, whenever an update is made to the EMP table, the new value of the salary of the employee, referenced in the before-trigger transition variable T1.SALARY, is checked against the salary of that employee's manager. This is done by joining the employee table to the department table, using the transition variable T1.WORKDEPT to get the department information for the employee being updated. The EMP table is then joined, using the manager's employee number in order to get the salary of the manager.

Transition Tables

Transition tables allow after triggers to access a set of affected rows and see how they were before the triggering operation and then see all rows after the triggering operation. Transition tables are also implemented using the REFERENCING clause in the trigger definition:

 REFERENCING OLD_TABLE AS OLD_ACCT_TABL NEW_TABLE AS NEW_ACCT_TABLE 

NOTE

Transition tables are materialized in DSNDB07.

Transition tables allow an SQL statement embedded in the trigger body to access the entire set of affected data in the state it was in before or after the change. In the following example, a fullselect reads the entire set of changed rows to pass qualifying data to a user-defined function:

 CREATE TRIGGER EMPMRGR AFTER UPDATE ON EMP REFERENCING NEW_TABLE AS NTABLE FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC    SELECT SALARYALERT(EMPNO, SALARY)    FROM NTABLE    WHERE SALARY > 150000; END; 

Transition tables can also be passed to stored procedures and UDFs that are invoked within the body of the trigger. The table is not passed as a parameter; instead, a table locator is passed, which can then be used to establish a cursor within the stored procedure or UDF. The following example demonstrates the passing of a transition table to a UDF:

 CREATE TRIGGER EMPMRGR AFTER UPDATE ON EMP REFERENCING NEW_TABLE AS NTABLE FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC    VALUES (SALARYALERT(TABLE NTABLE)); END; 

The corresponding function definition would look something like this:

 CREATE FUNCTION SALARYALERT (TABLE LIKE EMP AS LOCATOR)    RETURNS INTEGER    EXTERNAL NAME SALERT    PARAMETER STYLE DB2SQL    LANGUAGE C; 

The C program would declare a cursor against the transition table by referencing the locator variable that was passed as a parameter in place of a table reference:

 DECLARE C1 CURSOR FOR    SELECT EMPNO, SALARY    FROM TABLE(:LOC1 LIKE EMP)    WHERE SALARY > 150000; 

Once the input locator parameter is accepted into the :LOC1 variable, the cursor can be opened and processed.

Allowable Combinations

Although different combinations of trigger options are available, not all are compatible. Table 15-1 shows the valid combinations for trigger options.

Table 15-1. Trigger-Option Combinations

Granularity	Activation Time	Trigger Operation	Transition Variables Allowed	Transition Tables Allowed
Row	Before	INSERT	New	None
		UPDATE	Old, new
		DELETE	Old
	After	INSERT	New	NEW_TABLE
		UPDATE	Old, new	OLD_TABLE, NEW_TABLE
		DELETE	Old	OLD_TABLE
Statement	Before	Invalid	None	Invalid
	After	INSERT		NEW_TABLE
		UPDATE		OLD_TABLE, NEW_TABLE
		DELETE		OLD_TABLE

Trigger Packages

When a trigger is created, DB2 creates a trigger package, which is different from a package created for an application program. (For more information on packages, refer to Chapter 11.) Trigger packages can be rebound locally, but you cannot bind them; binding is done automatically during creation. The package can be rebound only with the REBIND TRIGGER PACKAGE command, and this will allow you to change subsets of default bind options (CURRENTDATA, EXPLAIN, FLAG, ISOLATION, RELEASE). For more information on the bind options, refer to Chapter 11.

Trigger packages cannot be copied, freed, or dropped. In order to delete a trigger package, the DROP TRIGGER SQL statement must be issued.

NOTE

Rebinding trigger packages after creation is also useful for picking up new access paths.

The qualifier of the trigger name determines package collection. For static SQL, the authorization ID of the QUALIFIER bind option will be the qualifier; for dynamic SQL, whatever the CURRENT SQLID is will be the qualifier if not specified explicitly in the statement.

Trigger Invalidations

Invalid updates can be detected and stopped by triggers in a couple of ways. You can use the SIGNAL SQLSTATE statement or the RAISE_ERROR function.

SIGNAL SQLSTATE is a new SQL statement used to cause an error to be returned to the application with a specified SQLSTATE code and a specific message to stop processing. This statement can be used only as a triggered SQL statement within a trigger and can be controlled with a WHEN clause. The following example shows the use of the SIGNAL statement:

 WHEN NEW_ACCT.AMOUNT < (OLD_ACCT.AMOUNT)    SIGNAL SQLSTATE '99001' ('Bad amount field') 

If you use the SIGNAL statement to raise an error condition, a rollback will also be performed to back out the changes made by an SQL statement, as well as any changes caused by the trigger, such as cascading effects resulting from a referential relationship. SIGNAL can be used in either before or after triggers. Other statements in the program can be either committed or rolled back.

RAISE_ERROR is not a statement but a built-in function that causes the statement that includes it to return an error with a specific SQLSTATE, SQLCODE 438, and a message. This function does basically the same thing as the SIGNAL statement and can be used wherever an expression can be used. The RAISE_ERROR function always returns null with an undefined data type and is most useful in CASE expressions, especially when they are used in a stored procedure. The following example shows a CASE expression with the RAISE_ERROR function:

 VALUES (CASE   WHEN NEW_ACCT.AMOUNT < OLD_ACCT.AMOUNT   THEN RAISE_ERROR('99001', 'Bad amount field')) 

Performance Issues

Recursive triggers are updates applied by a trigger, causing it to fire again. Recursive triggers can easily lead to loops and can be very complex statements. However, this may be required by some applications for related rows. You will need code to stop the trigger.

The ordering of multiple triggers can be an issue because the same type of triggers on the same table are activated in the order created, identified in the creation timestamp. The interaction among triggers and referential constraints can also be an issue, because the order of processing can be significant on results produced.

Invoking stored procedures and UDFs from triggers raises performance and manageability concerns. Triggers can include only SQL but can call stored procedures and UDFs, which are user-written and therefore have many implications for integrity and performance. Transition tables can be passed to stored procedures and UDFs also.

Trigger cascading occurs when a trigger could modify the triggering table or another table. Triggers can be activated at the same level or different levels; when activated at different levels, cascading occurs. Cascading can occur only for after triggers. Cascading can occur for UDFs, stored procedures, and triggers for up to 16 levels.

Monitoring Triggers

The actions of triggers can be monitored in various ways. The DB2 Performance Expert statistics and accounting reports show the number of times a trigger has been activated, a row trigger was activated, and an SQL error occurred during the execution of a triggered action.

Other details can be found in the traces. For example, IFCID 16 has information about the materialization of a work file in support of a transition table, where tr is the transition table for triggers. Other information in IFCID 16 includes the depth level of the trigger (016), where 0 indicates that there are no triggers. You can also find the type of SQL that invoked the trigger:

• I = INSERT

• U = INSERT into a transition table because of an update

• D = INSERT into a transition table because of a delete

The type of referential integrity (RI) that caused an insert into a transition table for a trigger is also indicated with an S for SET NULLcan occur when the type is Updateor C for CASCADE DELETEcan occur when the type is Delete).

If a transition table needs to be scanned for a trigger, you can find this occurrence in IFCID 17: tr for transition table scan for a trigger.

Catalog Information

The SYSIBM.SYSTRIGGERS catalog table contains information about the triggers defined in your databases. To find all the triggers defined on a particular table and the characteristics of each trigger and to determine the order in which the triggers are executed, you can issue the following query:

 SELECT DISTINCT SCHEMA, NAME, TRIGTIME, TRIGEVENT,        GRANULARITY, CREATEDTS FROM SYSIBM.SYSTRIGGERS WHERE TBNAME = table-name   AND TBOWNER = table-owner ORDER BY CREATEDTS 

You can get the text of the trigger with the following statement:

 SELECT TEXT, SEQNO FROM SYSIBM.SYSTRIGGERS WHERE SCHEMA = schema_name   AND NAME = trigger_name ORDER BY SEQNO  

Triggers versus Table-Check Constraints

If a trigger and a table-check constraint can enforce the same rule, it is better to use a table-check constraint to enforce business rules. You would want to explore the use of triggers only when a constraint is not flexible enough to enforce a business rule. Constraints and declarative RI are more useful when you have only one state to enforce in a business rule. Although triggers are more powerful than table-check constraints and can be more extensive in terms of rule enforcement, constraints can be better optimized by DB2.

Table-check constraints are enforced for all existing data at the time of creation and are enforced for all statements affecting the data. A table-check constraint is defined on a populated table, using the ALTER TABLE statement, and the value of the CURRENT RULES special register is DB2. Constraints offer a few other advantages over triggers: Constraints are written in a less procedural way than triggers and are better optimized. Constraints protect data against being placed into an invalid state by any kind of statement, whereas a trigger applies only to a specific kind of statement, such as an update or a delete.

Because they can enforce several rules that constraints cannot, triggers are more powerful than check constraints. You can use triggers to apply rules that involve different states of data, perhaps where you need to know the state of the data before and after a calculation.

Triggers and Declarative RI

Trigger operations may result from changes made to enforce DB2-enforced referential constraints. For example, if you are deleting an EMPLOYEE table row that causes cascaded deletes to the PAYROLL table through referential constraints, the delete triggers that are defined on the PAYROLL table are subsequently executed. The delete triggers are activated as a result of the referential constraint defined on the EMPLOYEE table. This may or may not be the desired result, so you need to be aware of cascading effects when using triggers. When triggers cascade in this manner, they are considered to be at the same nesting level so the 16-level limit will not interfere with long chains of cascading deletes.

Triggers and UDFs

You can use a UDF in a trigger, and these types of functions can help to centralize rules to ensure that they are enforced in the same manner in current and future applications. To invoke a UDF in a trigger, the VALUES clause has to be used. Figure 15-2 is an example of how to invoke a UDF in a trigger.

Figure 15-2. Invoking a UDF from a trigger

In the following example, PAGE_DBA is a user-written program, perhaps in C or Java, that formulates a message and triggers a process that sends a message to a pager:

 BEGIN ATOMIC    VALUES(PAGE_DBA('Table spaces:' CONCAT TS.NAME,       'needs to be reorged NOW!')); END 

Using these kinds of UDFs in triggers enables them to perform any kind of task and not be limited to SQL.

Dropping Triggers

Triggers can be dropped by using the DROP TRIGGER statement. If the table on which the trigger is defined is dropped, so is the trigger. Regardless of what caused the trigger to be dropped, the information about the trigger is removed from SYSIBM.SYSTRIGGERS and SYSIBM.SYSPACKAGE. It is a good idea to keep a copy of the trigger code so it can be recreated if dropped.