The CREATE TRIGGER Statement

Triggers are database objects associated with a table or a view to define operations that should occur automatically upon an INSERT, UPDATE, or DELETE operation (hereafter called the triggering SQL statement) on that table or view. Operations performed by triggers occur within the database engine and are therefore transparent to the application.

There is often application logic (or rules) that should always be enforced across all applications. These rules may exist because data in one table may be related to data in others. If you have many applications that share a set of tables, it can be cumbersome to ensure that all applications follow and enforce these logic rules consistently and properly. To compound the problem, if the logic or rules change, application code changes are required for all affected applications.

Triggers can solve this problem by moving logic from the application level to the database level so that all applications share the same code which enforces these rules. If there is ever a change in the rules, you only need to change the trigger definitions in the database, and all applications will follow the new rules without requiring any additional changes.

Here are some examples of how triggers might be used:

When inserting, triggers can be used to supply, validate, or manipulate data before allowing an insert operation to occur.
When updating, triggers can be used to compare the new value (supplied by the UPDATE statement) with the existing value in the table to ensure that the transition from old value to new value follows proper state transitions. For example, a trigger could be created to allow a column value to change from NO to YES only and not vice versa.
Upon deletion, triggers can be used to automatically insert logging information into another table for audit trail purposes.

On LUW, the INSTEAD OF triggers can be used to insert, update, or delete from views where these operations are otherwise not allowed. The views are not insertable, updatable, or deletable because the columns of the views cannot be automatically mapped to underling table columns. However, if you know your business logic and you know how the changes in views can be mapped to changes in underlying tables, you can put the logic into the body of INSTEAD OF triggers and use INSTEAD OF triggers to work around SQL limits.

Tip

On LUW, use INSTEAD OF triggers to support insert, update, or delete statements through the views, which are otherwise not insertable, updatable, or deletable.

The complete syntax of the CREATE TRIGGER statement is shown in Figure 9.16.

Figure 9.16. The complete CREATE TRIGGER statement syntax for LUW.

 >>-CREATE TRIGGER--trigger-name--+-NO CASCADE BEFORE-+---------->                                  +-AFTER-------------+                                  '-INSTEAD OF--------' >--+-INSERT-------------------------+--ON--+-table-name-+------->    +-DELETE-------------------------+      '-view-name--'    '-UPDATE--+--------------------+-'              |    .-,-----------. |              |    V             | |              '-OF---column-name-+-' >--+------------------------------------------------------------------+-->    |              .-------------------------------------------------. |    |              V                      .-AS-.                     | |    '-REFERENCING------------------+-OLD--+----+--correlation-name-+-+-'                                   |      .-AS-.                   |                                   +-NEW--+----+--correlation-name-+                                   |            .-AS-.             |                                   +-OLD_TABLE--+----+--identifier--+                                   |            .-AS-.             |                                   '-NEW_TABLE--+----+--identifier--' >--+-FOR EACH ROW--------------+--MODE DB2SQL------------------->    '-FOR EACH STATEMENT--------' >--| triggered-action |---------------------------------------->< triggered-action: |--+-------------------------------------+---------------------->    '--------WHEN--(--search-condition--)-' >--+---------+----SQL-procedure-statement-----------------------|    '--label--'

Trigger Name

The unqualified trigger name specified in the CREATE TRIGGER statement is limited to 18 characters for LUW and 128 characters for iSeries and zSeries. It must begin with a letter, followed by zero or more letters, digits, or underscore characters (_).

A trigger is said to be qualified if it is referenced by a two-part name that is made up of a schema and a trigger name. The schema name limitation of triggers is the same as that of SQL procedures. The qualified trigger name, including the implicit or explicit schema name, must be unique.

BEFORE, AFTER, or INSTEAD OF

DB2 supports triggers defined on tables on all platforms, and triggers defined on views for LUW only. The view trigger is also known as the INSTEAD OF trigger. There are two types of table triggers: BEFORE and AFTER triggers. When a trigger begins its execution because of a triggering SQL statement, the trigger is said to be activated.

A BEFORE trigger will be activated before any table data is affected by the triggering SQL statement. You would use BEFORE triggers to intercept data provided by the application to validate or supply missing values, for example. If the triggering SQL statement affects more than one row, the BEFORE trigger will be activated for every row that is affected.

A BEFORE TRigger is always defined with the NO CASCADE clause. This means that operations performed within this trigger do not activate other triggers in the database. This has the implicit restriction that no INSERT, UPDATE, or DELETE statements are allowed in the body of BEFORE TRiggers. If you want to perform INSERT, UPDATE, or DELETE statements in a trigger body, you must define them as AFTER triggers.

Tip

NO CASCADE is optional and is the default behavior on iSeries. Always specify NO CASCADE for BEFORE triggers for portability.

AFTER triggers are activated after the triggering SQL statement has executed to completion successfully. In general, you would use AFTER triggers to post-process data. You would also use AFTER triggers if your logic required you to perform any INSERT, UPDATE, or DELETE operations because these statements are not supported in BEFORE TRiggers. AFTER triggers also offer additional options in how they can behave. With AFTER TRiggers, you can optionally define them to activate on a per-statement basis rather than on a per-row basis. This topic is discussed further in the upcoming sections in this chapter.

On LUW, an INSTEAD OF TRigger is activated after the triggering SQL statement has been issued to the base view. A fundamental difference exists between a table trigger and a view trigger. For a table trigger, regardless of a BEFORE or an AFTER trigger, both the trigger actions and the triggering statements are executed eventually. For a view trigger, also known as an INSTEAD OF TRigger, the trigger action is used to replace the triggering statement. In other words, the trigger action is executed instead of the triggering statement. The triggering statement is only attempted on the base view and is only used to fire the INSTEAD OF trigger. The triggering statement will never be executed.

INSERT, DELETE, or UPDATE

Triggers can be defined for INSERT, DELETE, or UPDATE triggering statements. On LUW and zSeries, there is no limit to how many triggers you can define on a table. On iSeries, the limit is 300. However, it is not a good idea to define too many triggers. When more than one trigger is defined for one type of triggering statementfor example, for an INSERT statementthey are fired in the order of their creation.

Tip

Consolidate your triggers into one if the result depends on the trigger firing sequence. You can explicitly control the sequences of the trigger actions within one trigger. The firing sequence can be changed when some triggers involved are rebuilt in a different order.

On iSeries, a DELETE trigger cannot be created on a table that has a referential constraint of ON DELETE SET NULL. Similarly, an UPDATE trigger cannot be created on a table that has a referential constraint of ON UPDATE SET NULL or ON UPDATE SET DEFAULT.

On LUW, you can only define one INSTEAD OF TRigger for each type of triggering statements on each view. In other words, you can only define one INSTEAD OF delete trigger, one INSTEAD OF insert trigger, and one INSTEAD OF update trigger for a view.

Tip

If you need to create more than one INSTEAD OF trigger for one type of triggering statement (INSERT, UPDATE, or DELETE), you can create two identical views with different names.

You should always try to specify the column name list for UPDATE triggers. If the optional column name list is not specified in an UPDATE trigger, every column of the table is implied. Omission of the column-name list implies that the trigger will be activated by the update of any column of the table. The column-name list is not supported in INSTEAD OF triggers.

Tip

Define an optional column-name list for your UPDATE triggers to prevent your triggers being activated unnecessarily.

REFERENCING Clauses

REFERENCING NEW is used to define a qualifier to reference transition values supplied by INSERT and UPDATE statements. REFERENCING OLD is used to define a qualifier to reference transition data that will be discarded by UPDATE and DELETE statements. REFERENCING OLD_TABLE specifies a transition table name that identifies the set of affected rows prior to the triggering SQL operation. REFERENCING NEW_TABLE specifies a transition table name that identifies the affected rows as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed. This option can be referred to in AFTER triggers only.

FOR EACH ROW or FOR EACH STATEMENT

Triggers defined with FOR EACH ROW will fire once for each row. Both BEFORE and AFTER TRiggers support activation FOR EACH ROW.

Triggers defined with FOR EACH STATEMENT will fire once for each statement only, no matter how many rows are affected in that statement. Only AFTER triggers can be defined to activate FOR EACH STATEMENT. For update and delete triggers defined with FOR EACH STATEMENT, they will be activated even if no rows are affected by the triggering UPDATE or DELETE statement. For example, if a single delete statement results in 1,000 rows deleted, an AFTER/FOR EACH STATEMENT TRigger defined on that table will activate just once at the end of the entire delete operation. On the other hand, if the trigger was defined as AFTER/FOR EACH ROW, it will activate 1,000 times, once for each row affected by the triggering SQL statement.

MODE DB2SQL

MODE DB2SQL is simply a clause that must be included as part of the CREATE TRIGGER syntax. DB2SQL is the only mode currently supported on LUW and zSeries. For iSeries, mode DB2ROW is also supported. Triggers on iSeries defined with mode DB2ROW are activated on each row operation whereas triggers defined with mode DB2SQL are activated after all of the row operations have been activated.

Triggered Action

On LUW, the SQL procedure statements in the body of the triggered action are implemented by inline SQL PL. The previous sections in this chapter on user-defined SQL functions have already explained the inline SQL PL and its limitations. For more information, refer to Appendix B, "Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows."

On iSeries, you can specify processing options to be used to create the trigger by using the SET OPTION clause. This clause can be specified as part of the triggered action, just before the WHEN clause.

The optional WHEN clause defines the conditions for trigger activation. You could, for example, define the trigger to activate only if certain data existed in another table. For better performance, it is recommended that you specify trigger activation conditions with the WHEN clause if it is reasonable to do so. The WHEN clause is not supported in INSTEAD OF TRiggers.

Tip

Use the WHEN clause to define the trigger activation condition to prevent your triggers being activated the unnecessarily.

Note

On zSeries, labels are not allowed in triggers.