Free SQL Help for Devs, DBAs, and the Curious

Triggers are an essential component of database management, providing an automated response to events, such as table updates, that can aid in maintaining data integrity and enforcing business rules. In this blog post, we will focus on DB2 triggers and how they can be used for audit enforcement. Although our focus is DB2, the concepts we will explore are also applicable in other database systems such as SQL Server 2019, MySQL 8.0, Azure SQL, and Azure Synapse.

Triggers in DB2 are actions that the database automatically performs in response to specific events. They are useful for maintaining complex integrity constraints that cannot be defined solely through primary and foreign keys. Triggers can also be used to enforce business rules and are especially useful for auditing purposes, allowing you to automatically track changes in your database.

Let's look at a simple example of a trigger in DB2. Suppose we have a table called EMPLOYEE and we want to keep track of changes to the SALARY column. We can create a trigger that inserts a record into an AUDIT table every time a change is made to an employee's salary. Here's what that might look like:

CREATE TRIGGER audit_salary
AFTER UPDATE OF SALARY ON EMPLOYEE
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
  INSERT INTO AUDIT (EMP_ID, OLD_SALARY, NEW_SALARY, CHANGE_DATE)
  VALUES (OLD.EMP_ID, OLD.SALARY, NEW.SALARY, CURRENT DATE);
END;

This trigger executes after an update of the SALARY column on the EMPLOYEE table. For each row affected by the update, it inserts a new row into the AUDIT table with the employee's ID, the old salary, the new salary, and the date of the change.

While this example is specific to DB2, similar triggers can be created in other database systems. For instance, in SQL Server 2019, the syntax is slightly different, but the concept is the same. Instead of "AFTER UPDATE OF SALARY", you would use "AFTER UPDATE" and then check if the SALARY column has been updated using the UPDATE() function.

CREATE TRIGGER audit_salary
ON EMPLOYEE
AFTER UPDATE
AS
IF UPDATE(SALARY) 
BEGIN
  INSERT INTO AUDIT (EMP_ID, OLD_SALARY, NEW_SALARY, CHANGE_DATE)
  SELECT INSERTED.EMP_ID, DELETED.SALARY, INSERTED.SALARY, GETDATE()
  FROM INSERTED, DELETED;
END;

In MySQL 8.0, the syntax is again slightly different, but the basic concept remains the same. Here's how you might create a similar trigger in MySQL:

CREATE TRIGGER audit_salary
AFTER UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
  INSERT INTO AUDIT (EMP_ID, OLD_SALARY, NEW_SALARY, CHANGE_DATE)
  VALUES (OLD.EMP_ID, OLD.SALARY, NEW.SALARY, NOW());
END;

In conclusion, triggers are a powerful tool for enforcing business rules and maintaining data integrity in a database. They can be particularly useful for auditing purposes, as they allow you to automatically track changes to your data. Despite the differences in syntax, the underlying concepts apply across different database systems, making them a versatile and valuable tool in any DBA's toolkit.