Creating a Trigger for Audit Logging

Create a Trigger for Audit Logging

Write a SQL query to create a trigger that logs all insert, update, and delete operations on a table.

Solution:

-- Create a trigger for audit logging.
CREATE TRIGGER trg_AuditEmployeeChanges
ON Employees -- Specify the table to monitor.
AFTER INSERT, UPDATE, DELETE -- Trigger fires after insert, update, or delete operations.
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        -- Log updates.
        INSERT INTO EmployeeAudit (EmployeeID, Operation, ChangeDate)
        SELECT i.EmployeeID, 'UPDATE', GETDATE()
        FROM inserted i;
    END
    ELSE IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        -- Log inserts.
        INSERT INTO EmployeeAudit (EmployeeID, Operation, ChangeDate)
        SELECT i.EmployeeID, 'INSERT', GETDATE()
        FROM inserted i;
    END
    ELSE IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        -- Log deletes.
        INSERT INTO EmployeeAudit (EmployeeID, Operation, ChangeDate)
        SELECT d.EmployeeID, 'DELETE', GETDATE()
        FROM deleted d;
    END
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a trigger that logs all changes (inserts, updates, and deletes) made to the Employees table.

2. Key Components :

1. AFTER INSERT, UPDATE, DELETE : Specifies when the trigger should fire.
2. Inserted and Deleted Tables : Track old and new values for updates, inserts, and deletes.
3. EmployeeAudit Table : Logs the changes for auditing purposes.

3. Why Use Audit Logging? :

1. Audit logging ensures accountability and provides a history of changes for compliance and debugging.

4. Real-World Application :

1. For example, in financial systems, you might use this trigger to log all changes to sensitive data like salaries.

Additional Notes:

• Ensure the audit table (EmployeeAudit) is properly indexed to maintain performance.
• Use this exercise to teach the importance of tracking changes for regulatory compliance.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a trigger that logs all changes made to a table, including old and new values, into a comprehensive audit trail.
• Write a SQL query to create a trigger that captures metadata about each change, such as the user who made the change and the timestamp.
• Write a SQL query to create a trigger that logs changes to sensitive fields separately for enhanced security monitoring.
• Write a SQL query to create a trigger that archives deleted records into a historical table instead of permanently removing them.

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

Previous SQL Exercise: Create a Table-Valued Function with Joins.
Next SQL Exercise: Create a Scalar Function with Aggregation.