A trigger is a set of actions that will be executed when a defined event occurs. These are known as active triggers. The triggering events can be the following SQL statements:
Triggers are defined for a specific table and once defined, a trigger is automatically active. A table can have multiple triggers defined for it, and if multiple triggers are defined for a given table, the order of trigger activation is based on the trigger creation timestamp (the order in which the triggers were created). Trigger definitions are stored in the DB2 catalog tables.
Some of the uses of a trigger include the following:
- Data Validation: Ensures that a new data value is within the proper range. This is similar to table-check constraints, but it is a more flexible data-validation mechanism.
- Data Conditioning: Implemented using triggers that fire before data record modification. This allows the new data value to be modified or conditioned to a predefined value.
- Data Integrity: Can be used to ensure that cross-table dependencies are maintained.
The triggered action could involve updating data records in related tables. This is similar to referential integrity, but it is a more flexible alternative. We can also use triggers to enforce business rules, create new column values or edit column values, validate all input data, or maintain summary tables or cross-reference tables. They provide for enhanced enterprise and business functionality and faster application development and global enforcement of business rules.
Triggers are defined using the CREATE TRIGGER statement, which contains many options. The primary options are whether it is a before trigger or an after trigger, whether it is a row trigger or a statement trigger, and the language of the trigger. The language is currently only SQL, but that will probably change in the future. There is even rumor that the SQL procedure language is a candidate for triggers. The phrase MODE DB2SQL is the execution mode of the trigger. This phrase is required for each trigger to ensure that an existing application will not be negatively impacted if alternative execution modes for triggers are added to DB2 in the future. You can have up to 12 types of triggers on a single table.
NOTE: Triggers get invoked in the order they were created! A timestamp is recorded when the trigger is created (and recreated). A DROP and (re)CREATE of a trigger can completely mess up your process by changing the order in which triggers are executed. Be careful!
When adding triggers, the rows that are in violation of a newly added trigger will not be rejected. When a trigger is added to a table that already has existing rows, it will not cause any triggered actions to be activated. If the trigger is designed to enforce some type of integrity constraint on the data rows in the table, those constraints may not be enforced by rules defined in the trigger (or held true) for the rows that existed in the table before the trigger was added.
If an update trigger without an explicit column list is created, packages with an update usage on the target table are invalidated. If an update trigger with a column list is created, packages with update usage on the target table are only invalidated if the package also has an update usage on at least one column in the column-name list of the CREATE TRIGGER statement. If an insert trigger is created, packages that have an insert usage on the target table are invalidated. If a delete trigger is created, packages that have a delete usage on the target table are invalidated.
There is a lot of functionality that can be used within a trigger. For example, a CASE expression can be used in a trigger, but it needs to be nested inside a VALUES statement, as shown here:
BEGIN ATOMIC VALUES CASE WHEN condition THEN something WHEN other condition THEN something else END END;
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in April 2003