DB2 - Triggers

Db2 - Triggers

This chapter covers triggers, a powerful feature in Db2 for automating actions in response to data modification events.

Introduction to Triggers

A trigger is a named database object containing a set of SQL statements that are automatically executed, or ‘fired’, when a specific event occurs on a table or view. These events are data modification operations: INSERT, UPDATE, or DELETE.

Triggers are stored in the database and managed by the database server. This ensures that the defined business rules are consistently enforced, regardless of which application or user performs the data modification. They are commonly used for enforcing complex business rules, maintaining data integrity, and creating audit trails.

Types of Triggers

Triggers are primarily classified by their activation time relative to the triggering event:

• BEFORE Triggers: These fire before the INSERT, UPDATE, or DELETE operation is applied to the database. They are ideal for validating or modifying the incoming data before it is written. For example, you could use a BEFORE trigger to automatically set a created_at timestamp on a new row.
• AFTER Triggers: These fire after the operation has successfully completed. They are used for actions that should occur as a result of the data change, such as logging the change to an audit table or updating summary tables.
• INSTEAD OF Triggers: These are a special type of trigger defined on views, not tables. They fire instead of the triggering INSERT, UPDATE, or DELETE statement. They provide a mechanism to make non-updatable views (e.g., views based on joins) updatable by defining the correct logic to modify the underlying base tables.

Alternative to Triggers: Identity Columns

A very common use case for triggers in older systems was to generate unique primary key values from a sequence. Modern Db2 offers a much simpler and more efficient built-in mechanism for this: Identity Columns.

-- This is the recommended modern approach for auto-incrementing keys.
CREATE TABLE products (
    product_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    product_name VARCHAR(100),
    PRIMARY KEY (product_id)
);

-- Db2 automatically generates the ID. No trigger needed!
INSERT INTO products (product_name) VALUES ('Super Widget');

You should always prefer using an IDENTITY column over a BEFORE INSERT trigger for auto-incrementing keys.

Creating a BEFORE Trigger: Data Validation

Let’s create a BEFORE trigger to ensure that an employee’s salary can never be set to a negative value.

CREATE TRIGGER check_positive_salary
NO CASCADE BEFORE UPDATE ON employees
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.salary < 0)
BEGIN
    -- Signal an error to prevent the update
    SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'Salary cannot be negative.';
END;

Now, if a user tries to set a negative salary, the trigger will fire before the update occurs, and the SIGNAL statement will raise an error, causing the entire operation to fail.

Creating an AFTER Trigger: Auditing Changes

A classic use for an AFTER trigger is to create an audit trail. Let’s create a trigger that logs every salary change for an employee into a separate salary_audit table.

-- First, create the audit table
CREATE TABLE salary_audit (
    audit_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    changed_by VARCHAR(128),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Now, create the AFTER UPDATE trigger
CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN
    -- Insert a record into the audit table with old and new values
    INSERT INTO salary_audit(employee_id, old_salary, new_salary, changed_by)
    VALUES (o.employee_id, o.salary, n.salary, SESSION_USER);
END;

With this trigger in place, every UPDATE to the salary column in the employees table will automatically generate a corresponding audit record.

Dropping a Trigger

Dropping a trigger removes it permanently from the database.

Syntax:

DROP TRIGGER <trigger_name>;

Example:

DROP TRIGGER log_salary_change;

Best Practices and Common Pitfalls

• Keep Logic Simple: Triggers should contain simple, fast-executing logic. Complex business logic is often better placed in stored procedures.
• Beware of Performance: Triggers add overhead to every DML operation on a table. Overuse or inefficient trigger logic can severely degrade database performance.
• Avoid Cascading Triggers: Be cautious of creating triggers that cause other triggers to fire, which can lead to complex, hard-to-debug chains of events (cascading triggers) or even infinite loops (recursive triggers).
• Document Thoroughly: Since triggers execute implicitly, their existence can be non-obvious. Always document your triggers and their purpose.