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:
- The goal is to create a trigger that logs changes to the Employees table whenever an update occurs.
- CREATE TRIGGER : Defines the trigger.
- AFTER UPDATE : Specifies when the trigger should fire.
- inserted and deleted : System tables used to track old and new values.
- Triggers automate tasks like logging, auditing, or enforcing business rules without requiring manual intervention.
- For example, in financial systems, you might use this trigger to log salary changes for auditing purposes.
1. Purpose of the Query :
2. Key Components :
3. Why Use Triggers? :
4. Real-World Application :
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.