Db2 Trigger

Summary: in this tutorial, you will learn about Db2 triggers, some types of triggers in Db2, and the differences between triggers, stored procedures, and check constraint.

What is a trigger

A trigger is a piece of code that is automatically executed, or fired in response to a data modification event on a table. The data modification event include insert, update, delete driven by an INSERT, DELETE, UPDATE, and MERGE statement.

Db2 stores the triggers in the database catalog, where each trigger is attached to a single table. You cannot call a trigger directly, only Db2 can call the trigger when an event occurs on the table.

DB2 trigger types

DB2 supports the following type of triggers:

• INSERT trigger is activated when an INSERT or MERGE statement inserts data into the table.
• UPDATE trigger is activated when an UPDATE or MERGE statement modifies the data of a table.
• DELETE trigger is activated whenever a DELETE statement executes against the table.

For each type of trigger, Db2 further classifies as:

BEFORE triggers are fired before an UPDATE or INSERT operation, and you can change the values that are being updated or inserted before they are written to the database. Generally, you use these triggers for data cleansing and modification.

AFTER triggers are fired after an  INSERT,  UPDATE, or  DELETE operation. Typically, you use these types of triggers to maintain relationships between data or to keep audit trail information.

INSTEAD OF triggers define how to perform an  INSERT,  UPDATE, or  DELETE operation on a view to enable non-updateable views to become updatable views.

Triggers vs. stored procedures

Triggers are like stored procedures in that they contain procedure code, or logic.

The following table illustrates the main difference between stored procedures and triggers:

Triggers vs. check constraints

Triggers are similar to check constraints because triggers can be used to control the integrity of data in a table. Check constraints specify what data can be stored in a table, but triggers can do that, plus make changes to data.

Triggers can access data from other tables while check constraints can only access data in the table to which they belong.

Additionally, triggers have more information on the data changes because they can see both old and new values of the changed columns and take actions based on that information.

When to use triggers

You should use triggers whenever you cannot use check constraints to implement complex business rules.

Managing triggers

• Creating a new trigger – learn how to use the CREATE TRIGGER statement to create a new trigger in the database.
• Removing a trigger – describe how to use the DROP TRIGGER statement to remove a trigger from the database.