w3resource

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.

Go to:


PREV : SQL Transactions Concurrency Control Exercises Home
NEXT : Create a Table-Valued Function.



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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.