Embedded SQL Programming Guide

Why Use Triggers?

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 (see 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 and Value of Triggers

Using triggers in your database manager can result in:

1. 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.

2. 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.

3. Easier maintenance

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

[ DB2 List of Books | Search the DB2 Books ]