Creating triggers

A trigger defines a set of actions that are executed 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: After 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.
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 ; can be replaced by another special character like #. You can also precede the CREATE TRIGGER DDL with:
     --#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.

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.


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.

Procedure

To create a trigger from the command line, enter: 
   CREATE TRIGGER name
      action ON table_name
      operation
      triggered_action

Examples

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;