 Craig S. Mullins | |||||||||||||||||||
| March 1999 |
|||||||||||||||||||
|
Triggers and DB2 Version 6
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:
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
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.
| ||||||||||||||||||