How do you create a DML trigger to track changes to a table?
QuinnLw
Posted by QuinnLw
Last Updated: June 13, 2024
DML trigger
To create a DML (Data Manipulation Language) trigger to track changes to a table in a database, you will typically follow these steps. I'll provide an example using SQL, which is common across various relational database management systems (RDBMS) like PostgreSQL, MySQL, Oracle, and SQL Server. However, the exact syntax may vary slightly depending on the RDBMS you are using.
Step 1: Create a Change Tracking Table
First, you need a table that will store the changes. This typically includes fields like the type of action (INSERT, UPDATE, DELETE), the timestamp of the change, the ID of the record that was changed, and potentially the old and new values. For this example, let's say we are tracking changes to a Customers table. 
CREATE TABLE ChangeLog (
    ChangeID INT PRIMARY KEY AUTO_INCREMENT,
    ChangeType VARCHAR(10),
    ChangeTime DATETIME DEFAULT CURRENT_TIMESTAMP,
    CustomerID INT,
    OldValue TEXT,
    NewValue TEXT
);
Step 2: Create the Trigger
Next, you will create a trigger on the Customers table. This trigger will capture the necessary information whenever a change occurs.
Example for an UPDATE Trigger
Here’s an example of a trigger that tracks updates to the Customers table: 
CREATE TRIGGER trg_Customers_Update
AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN
    INSERT INTO ChangeLog (ChangeType, CustomerID, OldValue, NewValue)
    VALUES ('UPDATE', OLD.CustomerID, OLD.CustomerName, NEW.CustomerName);
END;
Example for an INSERT Trigger
For tracking inserts, you could create an insert trigger like this: 
CREATE TRIGGER trg_Customers_Insert
AFTER INSERT ON Customers
FOR EACH ROW
BEGIN
    INSERT INTO ChangeLog (ChangeType, CustomerID, NewValue)
    VALUES ('INSERT', NEW.CustomerID, NEW.CustomerName);
END;
Example for a DELETE Trigger
For tracking deletes, you'd create a delete trigger: 
CREATE TRIGGER trg_Customers_Delete
AFTER DELETE ON Customers
FOR EACH ROW
BEGIN
    INSERT INTO ChangeLog (ChangeType, CustomerID, OldValue)
    VALUES ('DELETE', OLD.CustomerID, OLD.CustomerName);
END;
Step 3: Test the Trigger
After creating the trigger, test it by performing DML operations (insert, update, delete) on the Customers table and then querying the ChangeLog table to ensure that changes are being logged correctly.
Example Operation
1. Insert a new customer: 
INSERT INTO Customers (CustomerName) VALUES ('John Doe');
2. Update a customer's name: 
UPDATE Customers SET CustomerName = 'John Smith' WHERE CustomerID = 1;
3. Delete a customer: 
DELETE FROM Customers WHERE CustomerID = 1;
4. Check the logs: 
SELECT * FROM ChangeLog;
Important Notes:
- Database-Specific Syntax: The exact syntax for creating triggers can vary by database (e.g., MySQL, PostgreSQL, SQL Server), so refer to your database documentation. - Performance Considerations: Be cautious when creating triggers that could have performance impacts, especially if your table experiences a high volume of transactions. - Error Handling: Consider how you will handle errors or exceptions in the trigger. - Auditing Requirements: Depending on your auditing requirements, you might want to log additional data, such as the user who made the change.
Default Image