Creating a Trigger to Log Table Updates in SQL

Create a Trigger for Logging Changes

Write a SQL query to create a trigger that logs changes to a table.

Solution:

-- Create a trigger to log updates to the Employees table.
CREATE TRIGGER trg_LogEmployeeUpdates
ON Employees -- Specify the table to monitor.
AFTER UPDATE -- Trigger fires after an update operation.
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
    SELECT 
        i.EmployeeID, 
        d.Salary AS OldSalary, 
        i.Salary AS NewSalary, 
        GETDATE() AS ChangeDate
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a trigger that logs changes to the Employees table whenever an update occurs.

2. Key Components :

1. CREATE TRIGGER : Defines the trigger.
2. AFTER UPDATE : Specifies when the trigger should fire.
3. inserted and deleted : System tables used to track old and new values.

3. Why Use Triggers? :

1. Triggers automate tasks like logging, auditing, or enforcing business rules without requiring manual intervention.

4. Real-World Application :

1. For example, in financial systems, you might use this trigger to log salary changes for auditing purposes.

Additional Notes:

• Triggers can introduce overhead, so use them judiciously.
• Ensure proper indexing on audit tables to maintain performance.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a trigger that logs all delete operations on a specific table into an audit table.
• Write a SQL query to create a trigger that prevents the deletion of records from a table if certain conditions are not met.
• Write a SQL query to create a trigger that automatically updates a related table when a new record is inserted into the main table.
• Write a SQL query to create a trigger that sends an alert when a specific column value exceeds a predefined threshold.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: SQL Transactions Concurrency Control Exercises Home
Next SQL Exercise: Create a Table-Valued Function.