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
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:
INSERTtrigger is activated when an
MERGEstatement inserts data into the table.
UPDATEtrigger is activated when an
MERGEstatement modifies the data of a table.
DELETEtrigger is activated whenever a
DELETEstatement 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
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
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:
|Attached to a single table||Yes||No|
|Return a result||No||Yes|
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.
- Creating a new trigger – learn how to use the
CREATE TRIGGERstatement to create a new trigger in the database.
- Removing a trigger – describe how to use the
DROP TRIGGERstatement to remove a trigger from the database.