Trigger Guidelines

 <  Day Day Up  >  

Triggers are a powerful feature of DB2 for z/OS and 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.

Try to Create Only One Trigger Per Type Per Table

Avoid creating multiple triggers of the same type for the same table ”for example, 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.

Trigger Limitations

There are limits on how triggers can be used. For example, you cannot define triggers on:

  • A DB2 system catalog table

  • A view

  • An alias

  • A synonym

  • Any table with a three-part name

You can create triggers on your PLAN_TABLE , DSN_STATEMNT_TABLE , or DSN_FUNCTION_TABLE . But you should not define INSERT triggers on these tables because the triggers will not be fired when DB2 adds rows to the tables.

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.

FOR EACH ROW Versus FOR EACH STATEMENT

Understand the implication of the granularity of the trigger. A statement-level trigger, 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 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 8.3 for a listing of the possible types of referential integrity.

Triggers can be coded, in lieu of declarative RI, to support all of the RI rules in Table 8.3. 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 8.3. Types of Referential Integrity

RI

Description

DELETE RESTRICT

If any rows exist in the dependent table, the primary key row in the parent table cannot 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 cannot 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 cannot be inserted into the dependent table unless a primary key value already exists in the parent table.

FK UPDATE RESTRICTION

A foreign key cannot 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.


NOTE

DB2 does not provide native declarative RI support for pendant delete or update cascade referential constraint processing.


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. The solution is to use transition variables to view the NEW and OLD data values.

Using the VALUES Statement with Triggers

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

 

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

This trigger invokes the UDF named NEWPROJ whenever a new project is inserted into the PROJ table.

Using the VALUES statement eliminates the need to use a SELECT statement to invoke the UDF. This can deliver a performance gain.

NOTE

If a negative SQLCODE is returned when the function is invoked, DB2 stops executing the trigger and rolls back any triggered actions that were performed.


Use Declarative RI

In general, if DB2 supports the declarative referential integrity processing that you require, use declarative RI DDL instead of triggers. It will be easier to develop and support. Use triggers to implement RI only when DB2 does not support the type of RI you require (for example, to implement pendant delete RI processing).

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.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net