DB2 10.5 for Linux, UNIX, and Windows
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
change the terminator in the CLP on the fly, the following syntax sets
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.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
Example
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;