Implementing salary change auditing trigger in PL/SQL

PL/SQL Trigger: Exercise-7 with Solution

Write a code in PL/SQL to design a trigger that captures changes made to specific columns and logs them in an audit table.

Sample Solution:

PL/SQL Code:

-- Create the employees table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_nameVARCHAR2(100),
salary NUMBER
);

-- Create the salary_audit table to store changes
CREATE TABLE salary_audit (
audit_id NUMBER PRIMARY KEY,
employee_id NUMBER,
old_salary NUMBER,
new_salary NUMBER,
change_date TIMESTAMP
);

-- Create a sequence for generating unique audit IDs
CREATE SEQUENCE seq_salary_audit START WITH 1 INCREMENT BY 1;

-- Create a trigger to capture changes in salary
CREATE OR REPLACE TRIGGER salary_change_audit
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary<>OLD.salary) -- Only capture changes in the salary column
DECLARE
v_audit_id NUMBER;
BEGIN
    -- Generate a unique audit ID
    SELECT seq_salary_audit.NEXTVAL INTO v_audit_id FROM DUAL;

    -- Insert the change details into the audit table
    INSERT INTO salary_audit (audit_id, employee_id, old_salary, new_salary, change_date)
    VALUES (v_audit_id, :OLD.employee_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP);
END;
/

Let’s see how the trigger functions:

PL/SQL Code:

-- Inserting a sample employee record
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (1, 'John Doe', 50000);

-- Updating the salary of the employee
UPDATE employees SET salary = 60000 WHERE employee_id = 1;

After executing the UPDATE statement, to get the captured audit information you can query the salary_audit.

PL/SQL Code:

SELECT * FROM salary_audit;

Output:

AUDIT_ID	EMPLOYEE_ID	OLD_SALARY	NEW_SALARY	CHANGE_DATE
1	1	50000		60000		17-AUG-23 10.06.21.631889 AM

Flowchart:

Explanation:

The provided PL/SQL code implementing this trigger, for any update that modifies an employee's salary will be logged in the salary_audit table, providing a comprehensive audit trail for monitoring salary changes.

Assume that the two tables employees and salary_audit are involved with this trigger.

A sequence seq_salary_audit is created to generate unique audit IDs for entries in the salary_audit table. This sequence ensures that each change recorded in the audit log has a distinct identifier.

The trigger salary_change_audit is defined to capture changes in the salary column which fires automatically after an UPDATE operation on the employees table when the salary value is altered.

Inside the trigger body, the unique audit_id is generated using the seq_salary_audit sequence. Then, an INSERT statement is used to record the details of the salary change into the salary_audit table.

Previous: Restricting total order amount using PL/SQL Triggers.
Next: Methods in PL/SQL for a "Person" class with display and age update.