Administration Guide

Creating a Trigger

A trigger defines a set of actions that are executed in conjunction with, or triggered by, an INSERT, UPDATE, or DELETE clause on a specified base table or a typed table. Some uses of triggers are to:

• Validate input data
• Generate a value for a newly-inserted row
• Read from other tables for cross-referencing purposes
• Write to other tables for audit-trail purposes

You cannot use triggers with nicknames.

You can use triggers to support general forms of integrity or business rules. For example, a trigger can check a customer's credit limit before an order is accepted or update a summary data table.

The benefits of using a trigger are:

• Faster application development: Because a trigger is stored in the database, you do not have to code the actions it does in every application.
• Easier maintenance: Once a trigger is defined, it is automatically invoked when the table that it is created on is accessed.
• Global enforcement of business rules: If a business policy changes, you only need to change the trigger and not each application program.

To create a trigger using the Control Center:

Expand the object tree until you see the Triggers folder. Right-click the Triggers folder, and select Create from the pop-up menu. Specify information for the trigger. Specify the action that you want the trigger to invoke, and click Ok.

To create a trigger using the command line, enter:

   CREATE TRIGGER <name>
      <action> ON <table_name>
      <operation>
      <triggered_action>

The following SQL statement creates a trigger that increases the number of employees each time a new person is hired, by adding 1 to the number of employees (NBEMP) column in the COMPANY_STATS table each time a row is added to the EMPLOYEE table.

   CREATE TRIGGER NEW_HIRED
      AFTER INSERT ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      UPDATE COMPANY_STATS SET NBEMP = NBEMP+1;

A trigger body can include one or more of the following SQL statements: INSERT, searched UPDATE, searched DELETE, full-selects, SET transition-variable, and SIGNAL SQLSTATE. The trigger can be activated before or after the INSERT, UPDATE, or DELETE statement to which it refers. Refer to the SQL Reference for complete syntax information on the CREATE TRIGGER statement. Refer to the Application Development Guide for information about creating and using triggers.

Note:

If the trigger is a BEFORE trigger, the column name specified by the triggered action may not be a generated column other than the identity column. That is, the generated identity value is visible to BEFORE triggers.

Trigger Dependencies

All dependencies of a trigger on some other object are recorded in the SYSCAT.TRIGDEP catalog. A trigger can depend on many objects. These objects and the dependent trigger are presented in detail in the SQL Reference discussion on the DROP statement.

If one of these objects is dropped, the trigger becomes inoperative but its definition is retained in the catalog. To revalidate this trigger, you must retrieve its definition from the catalog and submit a new CREATE TRIGGER statement.

If a trigger is dropped, its description is deleted from the SYSCAT.TRIGGERS catalog view and all of its dependencies are deleted from the SYSCAT.TRIGDEP catalog view. All packages having UPDATE, INSERT, or DELETE dependencies on the trigger are invalidated.

If the dependent object is a view and it is made inoperative, the trigger is also marked inoperative. Any packages dependent on triggers that have been marked inoperative are invalidated. (For more information, see Statement Dependencies When Changing Objects.)