Craig S. Mullins 

Return to Home Page

March 1999

 

Triggers and DB2 Version 6
 
By Craig S. Mullins
 
Most relational database management systems provide support for triggers. And IBM is adding trigger support to DB2 for OS/390 in Version 6 (announced in May 1998, to be generally available in June 1999). But just what is a trigger? If you have never had the opportunity to use them, their power may elude you at first. However, once you have used triggers, living without them can be unthinkable!
 
The Basics
Simply stated, a trigger is a piece of code that is executed in response to a data modification statement; that is, an insert, update, or delete. To be a bit more precise: triggers are event-driven specialized procedures that are stored in, and managed by the RDBMS. Each trigger is attached to a single, specified table. Triggers can be thought of as an advanced form of "rule" or "constraint" written using an extended form of SQL. A trigger can not be directly called or executed; it is automatically executed (or "fired") by the RDBMS as the result of an action — a data modification to the associated table.
 
Once a trigger is created it is always executed when its "firing" event occurs (update, insert, or delete). Therefore triggers are automatic, implicit, and non-bypassable.
 
Triggers Versus Stored Procedures
Triggers are also similar to stored procedures. Both consist of procedural logic that is stored at the database level. However, stored procedures are not event-driven and are not attached to a specific table. A stored procedure is explicitly executed by invoking a CALL to the procedure (instead of implicitly being executed like triggers). Additionally, a stored procedure can access many tables without being specifically associated to any of them.
 
DB2 has supported stored procedures since Version 4.
 
Why Use Triggers?
Triggers are useful for implementing code that must be executed on a regular basis due to a pre-defined event. By utilizing triggers, scheduling and data integrity problems can be eliminated because the trigger will be fired whenever the triggering event occurs. You need not remember to schedule or code an activity to perform the logic in the trigger. It happens automatically by virtue of it being in the trigger. This is true of both static and dynamic SQL; ad hoc and planned SQL.
 
Triggers can be implemented for many practical uses. Quite often it is impossible to code business rules into the database using only DDL. For example, DB2 does not support complex constraints (only value-based CHECK constraints) or various types of referential constraints (such as pendant DELETE processing or ON UPDATE CASCADE). Using triggers, a very flexible environment is established for implementing business rules and constraints in the DBMS. This is important because having the business rules in the database ensures that everyone uses the same logic to accomplish the same process.
 
Triggers can be coded to access and/or modify other tables, print informational messages, and specify complex restrictions. For example, consider the standard suppliers and parts application used in most introductory database texts. A part can be supplied by many suppliers and a supplier can supply many parts. Triggers can be used to support the following scenarios:

  • What if a business rule exists specifying that no more than three suppliers are permitted to supply any single part? A trigger can be coded to check that rows can not be inserted if the data violates this requirement.
  • A trigger can be created to allow only orders for parts that are already in stock. Or, maybe for parts that are already in stock or are on order and planned for availability within the next week.
  • Triggers can be used to perform calculations such as ensuring that the order amount for the parts is calculated appropriately given the suppliers chosen to provide the parts. This is especially useful if the order purchase amount is stored in the database as redundant data.
  • To curb costs, a business decision may be made that the low cost supplier will always be used. A trigger can be implemented to disallow any order that is not the current "low cost" order.
The number of business rules that can be implemented using triggers is truly limited only by your imagination (or, more appropriately, your business needs).
 
Additionally, triggers can access non-DB2 resources. This can be accomplished by invoking a stored procedure or a user-defined function that takes advantage of the OS/390 resource recovery services (RRS). Data stored in the non-DB2 resource can be accessed or modified in the stored procedure or user-defined function that is called.
 
When Does a Trigger Fire?
Two options exists for when a trigger can fire: before the firing activity occurs or after the firing activity occurs. DB2 supports both "before" and "after" triggers. A "before" trigger executes before the firing activity occurs; an "after" trigger executes after the firing activity occurs. In DB2 V6, "before" triggers are restricted because they cannot perform updates.
 
Knowing how the triggers in your database function is imperative. Without this knowledge properly functioning triggers can not be coded, supported, or maintained effectively.
 
Consider, for example, if the firing activity occurs before the trigger is fired. In other words, the update, insert, or delete occurs first-as a result of this action, the trigger logic is executed. If necessary, the trigger code can "roll back" the data modification. What if the trigger is fired before the actual firing event occurs? In this situation a roll back would not be required for the firing event code because it did not yet occur. However, a roll back may be required for any data modifications that occurred prior to this firing event within the same transaction.
 
Another interesting feature of DB2 V6 triggers is the order in which they are fired. If multiple triggers are coded on the same table, which trigger is fired first? It can make a difference as to how the triggers should be coded, tested, and maintained. The rule for order of execution is basically simple to understand, but can be difficult to maintain. For triggers of the same type, they are executed in the order in which they were created. For example, if two "delete" triggers are coded on the same table, the one that was physically created first, is executed first. Keep this in mind as you make changes to your database. If you need to drop the table and re-create it to implement a schema change, make sure you create the triggers in the desired (same) order to keep the functionality the same.
 
As can readily be seen, determining the procedural activity that is required when triggers are present can be a complicated task. It is of paramount importance that all developers are schooled in the firing methods utilized for triggers in DB2 V6.
 
Trigger Packages
When a trigger is executed, DB2 creates a trigger package for the statements in the triggered action. The trigger package is recorded in SYSIBM.SYSPACKAGE and has the same name as the trigger. The trigger package is always accessible and can be executed only when a trigger is activated by a triggering operation.
 
To delete the trigger package, you must use the DROP TRIGGER statement.
 
Triggers Can Fire Other Triggers
As we've already learned, a trigger is fired by an insert, update, or delete. However, a trigger can also contain insert, update, and delete logic within itself. Therefore, a trigger is fired by a data modification, but can also cause another data modification, thereby firing yet another trigger. When a trigger contains insert, update, and/or delete logic, the trigger is said to be a nested trigger.
 
Most DBMSs, however, place a limit on the number of nested triggers that can be executed within a single firing event. If this were not done, it could be quite possible to having triggers firing triggers ad infinitum until all of the data was removed from an entire database!
 
If referential integrity is combined with triggers, additional cascading updates and/or deletes can occur. If a delete or update results in a series of additional updates or deletes that need to be propagated to other tables then the update or delete triggers for the second table also will be activated.
 
This combination of multiple triggers and referential integrity constraints are capable of setting a cascading effect into motion, which can result in multiple data changes. DB2 V6 limits this cascading effect to 16 levels in order to prevent endless looping. If more than 16 levels of nesting occur, the transaction is aborted.
 
The ability to nest triggers provides an efficient method for implementing automatic data integrity. Because triggers generally can not be by-passed, they provide an elegant solution to the enforced application of business rules. Use caution, however, to ensure that the maximum trigger nesting level is not reached. Failure to heed this advice can cause an environment where certain types of updates can not occur!
 
Trigger Limitations
There are limits on what triggers can accomplish. As of DB2 V6, you cannot define triggers on:
  • a system catalog table
  • PLAN_TABLE
  • STATEMENT_TABLE
  • DSN_FUNCTION_TABLE
  • View
  • Alias
  • Synonym
  • Any table with a three-part name
Using Triggers to Implement Referential Integrity
One of the primary uses for triggers is to support referential integrity (RI). Although DB2 supports a very robust form of declarative RI, no current DBMS fully supports all possible referential constraints. This is true of DB2, as well. Refer to Table 1 for a listing of these possibilities.
 
Triggers can be coded, in lieu of declarative RI, to support all of the RI rules in Table 1. Of course, when you use triggers, it necessitates writing procedural code for each rule for each constraint, whereas declarative RI constraints are coded in the DDL that is used to create relational tables.
 
Table 1. Referential Integrity Rules
DELETE RESTRICT If any rows exist in the dependent table, the primary key row in the parent table can not be deleted.
DELETE CASCADE If any rows exist in the dependent table, the primary key row in the parent table is deleted, and all dependent rows are also deleted.
DELETE NEUTRALIZE If any rows exist in the dependent table the primary key row in the parent table is deleted, and the foreign key column(s) for all dependent rows are set to NULL as well.
UPDATE RESTRICT If any rows exist in the dependent table, the primary key column(s) in the parent table can not be updated.
UPDATE CASCADE If any rows exist in the dependent table, the primary key column(s) in the parent table are updated, and all foreign key values in the dependent rows are updated to the same value.
UPDATE NEUTRALIZE If any rows exist in the dependent table, the primary key row in the parent table is deleted, and all foreign key values in the dependent rows are updated to NULL as well.
INSERT RESTRICT A foreign key value can not be inserted into the dependent table unless a primary key value already exists in the parent table.
FK UPDATE RESTRICTION A foreign key can not be updated to a value that does not already exist as a primary key value in the parent table.
PENDANT DELETE When the last foreign key value in the dependent table is deleted the primary key row in the parent table is also deleted.

 
In order to use triggers to support RI rules, it is sometimes necessary to know the values impacted by the action that fired the trigger. For example, consider the case where a trigger is fired because a row was deleted. The row, and all of its values, has already been deleted because the trigger is executed after its firing action occurs. But if this is the case, how can we ascertain if referentially connected rows exist with those values?
 
The solution is provided in the form of two specialized aliases available only inside of triggers: NEW and OLD.
 
Each trigger can have one NEW view of the table and one OLD view of the table available. Once again, these "views" are accessible only from triggers. They provide access to the modified data by viewing information in the transaction log. The transaction log is a record of all data modification activity, automatically maintained by the DBMS.
 
Figure 2. Before and After Views of Table Activity
 

 
When an INSERT occurs, the NEW table contains the rows that were just inserted into the table to which the trigger is attached. When a DELETE occurs, the OLD table contains the rows that were just deleted from the table to which the trigger is attached. An UPDATE statement logically functions as a DELETE followed by an INSERT. Therefore, after an UPDATE, the NEW table contains the new values for the rows that were just updated in the table to which the trigger is attached; the OLD table contains the old values for the updated rows.
 
Therefore, the trigger can use these specialized NEW and OLD table views to query the affected data. Remember, too, that SQL data modification can occur a set-at-a-time. One DELETE or UPDATE statement can impact multiple rows. This must be taken into account when coding the actual trigger logic.
 
Additionally, the alias names, OLD and NEW, can be changed if so desired (for example, to INSERTED and DELETED, the names used by SQL Server).
 
Trigger Granularity
Because SQL is a set-level language any single SQL statement can impact multiple rows of data. For example, one DELETE statement can actually cause zero, one, or many rows to be removed. Triggers need to take this into account.
 
Therefore, there are two levels of granularity that a trigger can have: statement level or row level. A statement level trigger is executed once upon firing, regardless of the actual number of rows inserted, deleted, or updated. A row level trigger, once fired, is executed once for each and every row that is inserted, deleted, or updated.
 
Different business requirements will drive what type of trigger granularity should be chosen.
 
A Sample Trigger
Browse the sample trigger depicted in Figure 3. It is an update trigger, coded on the EMP table. This trigger implements a simple check to ensure that raises are less than 50%. When the new salary exceeds 50% of the prior salary, an error is raised.
 
Figure 3. Sample Trigger
 
 CREATE TRIGGER SALARY_UPDATE
   BEFORE UPDATE OF SALARY 
   ON EMP
   FOR EACH ROW MODE DB2SQL

 WHEN (NEW.SALARY > (OLD.SALARY * 1.5))
 BEGIN ATOMIC
   SIGNAL SQLSTATE '75001' ('Raise exceeds 50%');
 END;
 
The trigger executes once for each row. So if multiple rows are modified by a single update, the trigger will run multiple times, once for each row modified. Also, the trigger will be run BEFORE the actual modification occurs. Finally, take special notice how NEW and OLD are used to check values before and after the update.
 
Synopsis
Triggers are a powerful feature; they enable non-bypassable, event-driven logic to be intrinsically intermingled with data. As of Version 6, DB2 for OS/390 will support triggers. It is a wise course of action to learn about triggers and what they can provide today, so you can benefit from implementing triggers after you migrate to DB2 V6.

 
From DB2 Update (Xephon),  March 1999.
 


1999 Mullins Consulting, Inc. All rights reserved.
Home.