Trigger Guidelines

Triggers are a powerful feature of DB2 for OS/390. They enable non-bypassable, event-driven logic to be intrinsically intermingled with data. The following guidelines can be used to help you implement effective and efficient triggers for your DB2 databases and applications.

Naming Triggers

A trigger name, along with its schema, must be unique within the DB2 subsystem. The schema name that qualifies the trigger is the owner of the trigger. The schema name for the trigger cannot begin with the letters SYS, unless the schema name is SYSADM.

Because the trigger name is also used for the trigger package name, the trigger name cannot be the name of a package that already exists. For trigger packages, the schema of the trigger is used as the collection of the trigger package. The combination of SCHEMA.TRIGGER must not be the same as an independently existing COLLECTION.PACKAGE combination.

Keep It Simple

Each trigger should be coded to perform one and only one task. The trigger should be as simple as possible while still performing the desired task. Do not create overly complex triggers that perform multiple, complex tasks. It is far better to have multiple triggers, each performing one simple task, than to have a single, very complex trigger that performs multiple tasks. A simple trigger will be easier to code, debug, understand, and maintain when it needs to be modified.

Implement Triggers with Care

After a trigger is created, it impacts change processing for every user and program that modifies data in the table on which the trigger is defined. Because of this global nature of triggers, take great care to implement only thoroughly tested and debugged triggers.

Test Trigger Logic Outside the Trigger First

Whenever possible, test the SQL to be included in the trigger outside the trigger first. After the bugs and syntax errors have been eliminated, create the trigger using the debugged SQL.

This technique is not always possible. For example, if the SQL requires the NEW and OLD transition values or a transition table, those elements will not function outside the scope of the trigger.

Try to Create Only One Trigger Per Type Per Table

If possible, avoid creating multiple triggers of the same type for the same table. For example, avoid creating two INSERT triggers both having an AFTER activation time defined on the same table.

This guideline is necessary because you cannot specify the order in which the triggers will fire. Instead, DB2 will execute multiple triggers of the same type on the same table in the order in which the triggers were created. This order can be difficult to maintain if changes are required that cause the triggers to be dropped and re-created.

However, this guideline can go against the "Keep It Simple" guideline. You need to determine, on a case-by-case basis, whether having multiple triggers of the same type on the same table is easier to understand and maintain than a single, more complex trigger.

BEFORE Versus AFTER Triggers

Assign the trigger activation specification carefully. Remember that a BEFORE trigger cannot cascade and fire other triggers because it cannot UPDATE data and an AFTER trigger cannot change transition variables.

FOR EACH ROW Versus FOR EACH STATEMENT

Understand the implication of the granularity of the trigger. A statement-level trigger, that is one specifying FOR EACH STATEMENT, will only fire once. If you need to examine the contents of impacted columns, you will need a row-level trigger, one specifying FOR EACH ROW.

Also, remember that you cannot specify FOR EACH STATEMENT for a BEFORE trigger.

Using the VALUES Statement with Triggers

The VALUES statement can be used to introduce expressions to be evaluated without assigning the results to output variables. One of the best uses of the VALUES statement is to invoke a user-defined function from inside a trigger. For example,

CREATE TRIGGER NEWPROJ
  AFTER INSERT ON PROJ
  REFERENCING NEW AS P
  FOR EACH ROW
 MODE DB2SQL
BEGIN ATOMIC
 VALUES(ADDPROJ(P.PROJNO));
END

This trigger invokes the UDF named ADDPROJ whenever a new project is inserted into the PROJ table. The UDF may populate other tables with project information, create an audit trrail of new project codes, or perform any other processing that must occur whenever a new project is begun.

Using the VALUES statement to execute the user-defined function eliminates the need to use a SELECT statement to invoke the UDF. This should deliver a performance gain.

Name Transition Variables Appropriately

The transition variables for accessing OLD and NEW data values can be changed to any value you so desire. For example, you might use INSERTED for NEW and DELETED for OLD, to mimic the way Microsoft SQL Server and SYBASE use transition variables. This is especially useful if you have staff members who understand triggers on a DBMS other than DB2.

Use Triggers to Keep Redundant Data Synchronized

Another good use of triggers is to maintain redundant data when the database is denormalized. Triggers can be used to keep redundant data in separate tables in sync. This could be accomplished by choosing one table as the "table of record." This table would have triggers on it that cascade any modifications to the redundant data in other tables whenever the table of record is modified.

Combat the Hidden Logic Effect

When you code logic into a trigger it is effectively hidden from application developers. Of course, the developers can find the information in the DB2 Catalog, but this places an onerous burden on the developers. If developers are not aware that triggers exist on tables that they must modify, the potential for duplicate modification exists. For example, consider the impact of a trigger that keeps derived up-to-date. Perhaps the trigger increments a column named TOT_EMPS in a company statistics table every time a new employee row is added to the EMP table. If the developer is not aware that this occurs in the trigger it is possible that he will code the logic to increment the TOT_EMPS column in his program. This will cause employees to be double-counted.

To avoid this type of problem make sure that all triggers are documented in a central location accessible to all developers. The documentation should be easy to read and interpret. It should not just be a copy of the trigger code. Instead, the document should be ordered by table name and should contain an English description of the logic performed by triggers.

Finally, all triggers and application programs should go through a thorough design review process prior to be promoted to the production environment. This is required to avoid duplication of effort when triggers are used.