Types of Triggers in DB2 at 22:56

Types of Triggers in DB2

Trigger Types	Description
BEFORE trigger	Trigger is activated before any table data is affected by the triggering SQL statement. A BEFORE trigger is always defined in a way that operations performed by this trigger cannot activate other triggers in the database.
AFTER trigger	Trigger is activated after the triggering SQL statement has executed to successful completion. Depending on the trigger action, an AFTER trigger may cause other triggers to be invoked. DB2 allows this cascade effect for a maximum of 16 levels.
INSTEAD OF trigger	Trigger is defined on database views rather than tables. Instead of executing the trigger event against the subject view, the triggered actions defined in the trigger are executed. They are useful, particularly in cases when the view is too complex to support update operations natively. Masking SQL statements in INSTEAD OF triggers can also simplify application interfaces.

Triggers are particularly useful when certain business rules should always be enforced across applications. These rules may exist because data in one table is related to data in other tables. If there is ever a change in the rules, you need only change the trigger definitions at the database, and all applications will follow the new rules without any additional changes required.

A very good example of such a rule was demonstrated in the L8NITE application. Recall that when the cashier scans and adds a product at customer checkout, the product information is inserted into the PRODUCT_PURCHASES table. In the same transaction, the product inventory is also updated. Similarly, for a refund request, the same business rule applies. The information for the product being returned is inserted into the PRODUCT_PURCHASES table. The application updates the product inventory to reflect this change. Rather than code such logic repeatedly in different parts of the application, we can move it to a trigger, allowing the trigger action to take care of the product