DB2 Version 9.7 for Linux, UNIX, and Windows

Creating triggers

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 table or a typed table.

About this task

Use triggers 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 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.

Benefits:

Faster application development: Because a trigger is stored in the database, you do not have to code the actions that it performs 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.

Restrictions:

You cannot use triggers with nicknames.
If the trigger is a BEFORE trigger, the column name specified by the triggered action must not be a generated column other than an identity column. That is, the generated identity value is visible to BEFORE triggers.

When creating an atomic trigger, care must be taken with the end-of-statement character. The command line processor, by default, considers a ";" the end-of-statement marker. You should manually edit the end-of-statement character in your script to create the atomic trigger so that you are using a character other than ";". For example, the ";" could be replaced by another special character like "#". You can also precede the CREATE TRIGGER DDL with:

     --#SET TERMINATOR @

To change the terminator in the CLP on the fly, the following syntax will set it back:

    --#SET TERMINATOR

To create a trigger from the command line, enter:

   db2 -td <delimiter> -vf <script>

where the <delimiter> is the alternative end-of-statement character and the <script> is the modified script with the new <delimiter> in it.

To create a trigger from the command line, enter:

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

The following 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
       UPDATE COMPANY_STATS SET NBEMP = NBEMP+1;

A trigger body can include one or more of the following statements: INSERT, searched UPDATE, searched DELETE, fullselect, SET Variable, and SIGNAL SQLSTATE. The trigger can be activated before or after the INSERT, UPDATE, or DELETE statement to which it refers.