Your company needs to track all changes to employee salaries for compliance purposes. You need to automatically log salary changes to an audit table whenever an UPDATE occurs.
Task
Create a trigger that automatically inserts a record into an audit_log table whenever an employee's salary is updated in the employees table.
Sample Schema: employees
emp_id
name
salary
1
John Doe
70000
2
Jane Smith
65000
Sample Schema: audit_log
log_id
emp_id
old_salary
new_salary
change_date
Initially empty
Solution:
-- First, create the audit_log table
CREATE TABLE audit_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
emp_id NUMBER,
old_salary NUMBER,
new_salary NUMBER,
change_date DATE DEFAULT SYSDATE
);
-- Create the trigger
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (emp_id, old_salary, new_salary)
VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary);
END;
/
-- Test the trigger
UPDATE employees
SET salary = 75000
WHERE emp_id = 1;
This AFTER UPDATE trigger automatically fires whenever a salary is updated. It uses :OLD and :NEW pseudo-records to capture the before and after values. The audit trail is created automatically without requiring manual logging.
Use Cases: Compliance tracking, security auditing, change history, and debugging data issues.