Application Development Guide

Why Use Triggers?

In order to change your database manager from a passive system to an active one, use the capabilities embodied in a trigger function. A trigger defines a set of actions that are activated or triggered by an update operation on a specified base table. These actions may cause other changes to the database, perform operations outside DB2 (for example, send an e-mail or write a record in a file), raise an exception to prevent the update operation from taking place, and so on.

You can use triggers to support general forms of integrity such as business rules. For example, your business may wish to refuse orders that exceed its customers' credit limit. A trigger can be used to enforce this constraint. In general, triggers are powerful mechanisms to capture transitional business rules. Transitional business rules are rules that involve different states of the data.

For example, suppose a salary cannot be increased by more than 10 per cent. To check this rule, the value of the salary before and after the increase must be compared. For rules that do not involve more than one state of the data, check and referential integrity constraints may be more appropriate (refer to the SQL Reference for more information). Because of the declarative semantics of check and referential constraints, their use is recommended for constraints that are not transitional.

You can also use triggers for tasks such as automatically updating summary data. By keeping these actions as a part of the database and ensuring that they occur automatically, triggers enhance database integrity. For example, suppose you want to automatically track the number of employees managed by a company:

    Tables: EMPLOYEE (as in Sample Tables) 
      COMPANY_STATS (NBEMP, NBPRODUCT, REVENUE)

You can define two triggers:

• A trigger that increments the number of employees each time a new person is hired, that is, each time a new row is inserted into the table EMPLOYEE:

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

• A trigger that decrements the number of employees each time an employee leaves the company, that is, each time a row is deleted from the table EMPLOYEE:

      CREATE TRIGGER FORMER_EMP 
        AFTER DELETE ON EMPLOYEE 
        FOR EACH ROW MODE DB2SQL 
        UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1
  

Specifically, you can use triggers to:

• Validate input data using the SIGNAL SQLSTATE SQL statement, the built-in RAISE_ERROR function, or invoke a UDF to return an SQLSTATE indicating that an error has occurred, if invalid data is discovered. Note that validation of non-transitional data is usually better handled by check and referential constraints. By contrast, triggers are appropriate for validation of transitional data, that is, validations which require comparisons between the value before and after an update operation.
• Automatically generate values for newly inserted rows (this is known as a surrogate function). That is, to implement user-defined default values, possibly based on other values in the row or values in other tables.
• Read from other tables for cross-referencing purposes.
• Write to other tables for audit-trail purposes.
• Support alerts (for example, through electronic mail messages).

Benefits of Triggers

Using triggers in your database manager can result in:

• Faster application development.

  Because triggers are stored in the relational database, the actions performed by triggers do not have to be coded in each application.

• Global enforcement of business rules

  A trigger only has to be defined once, and then it can be used for any application that changes the table.

• Easier maintenance

  If a business policy changes, only the corresponding trigger needs to change instead of each application program.