What are Triggers and how do you implement them in DB2?

Triggers in DB2 are a powerful mechanism that allows you to automatically execute a set of SQL statements when a specific event occurs on a table. Think of them as event-driven actions that help you enforce business rules, maintain data integrity, and automate tasks.

Here's a breakdown of how triggers work and how to implement them in DB2:

What are Triggers?

Event-driven: Triggers are activated by events like inserting, updating, or deleting data in a table.
Automatic execution: Once defined, triggers automatically execute whenever the associated event occurs. You don't need to explicitly call them.
Enforce rules: Triggers can be used to enforce complex business rules that might be difficult to implement with constraints alone.
Maintain data integrity: They can ensure data consistency across related tables.
Automate tasks: Triggers can automate tasks like auditing changes or generating notifications.

Types of Triggers:

DB2 supports different types of triggers based on when they are activated:

BEFORE triggers: Execute before the triggering event (insert, update, or delete) takes place. Useful for validating data or modifying it before it's stored.
AFTER triggers: Execute after the triggering event. Useful for actions like updating related tables or logging changes.
INSTEAD OF triggers: Execute instead of the triggering event. Primarily used for views to allow modifications that wouldn't be directly possible otherwise.

Implementing Triggers in DB2:

You create triggers using the CREATE TRIGGER statement. Here's the basic syntax :

CREATE TRIGGER trigger-name
  {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
  ON table-name
  [REFERENCING {OLD ROW AS old-row-name} {NEW ROW AS new-row-name}]
  [FOR EACH {ROW | STATEMENT}]
  trigger-body

Let's break down the key parts :

trigger-name: The name you give to your trigger.
BEFORE | AFTER | INSTEAD OF: Specifies when the trigger executes.
INSERT | UPDATE | DELETE: The event that activates the trigger.
table-name: The table on which the trigger is defined.
REFERENCING: Allows you to refer to the old and new values of the row being modified (useful for BEFORE and AFTER triggers).
FOR EACH {ROW | STATEMENT}:
- ROW: The trigger executes for each row affected by the triggering event.
- STATEMENT: The trigger executes once for the entire SQL statement, even if it affects multiple rows.
trigger-body: The SQL code that will be executed when the trigger is activated.

Example:

Let's say you want to ensure that the OrderDate in your ORDERS table is never in the future. You can create a BEFORE trigger like this :

CREATE TRIGGER CheckOrderDate
  BEFORE INSERT ON ORDERS
  REFERENCING NEW ROW AS newOrder
  FOR EACH ROW
BEGIN
  IF newOrder.OrderDate > CURRENT DATE THEN
    SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'Order date cannot be in the future.';
  END IF;
END;

This trigger checks the OrderDate before a new row is inserted. If it's in the future, the trigger raises an error, preventing the insertion.