Auditing employee table changes with PL/SQL Trigger

PL/SQL Trigger: Exercise-8 with Solution

Write a code in PL/SQL to implement a trigger that records user activity (inserts, updates, deletes) in an audit log for a given set of tables.

Sample Solution:

PL/SQL Code:

-- Create Employee table
CREATE TABLE Employee (
emp_id NUMBER PRIMARY KEY,
emp_nameVARCHAR2(100),
emp_salary NUMBER
);
-- Create Audit_Log table
CREATE TABLE Audit_Log (
log_id NUMBER PRIMARY KEY,
table_nameVARCHAR2(100),
activity_typeVARCHAR2(20),
activity_date TIMESTAMP,
user_idVARCHAR2(50)
);
CREATE SEQUENCE Audit_Log_Seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER Employee_Audit_Trigger
AFTER INSERT OR UPDATE OR DELETE ON Employee
FOR EACH ROW
DECLARE
v_activity_typeVARCHAR2(20);
BEGIN
    IF INSERTING THEN
v_activity_type := 'INSERT';
    ELSIF UPDATING THEN
v_activity_type := 'UPDATE';
    ELSIF DELETING THEN
v_activity_type := 'DELETE';
    END IF;
    INSERT INTO Audit_Log (log_id, table_name, activity_type, activity_date, user_id)
    VALUES (Audit_Log_Seq.NEXTVAL, 'Employee', v_activity_type, SYSTIMESTAMP, USER);
END;
-- Insert a new employee
INSERT INTO Employee (emp_id, emp_name, emp_salary)
VALUES (1, 'John Doe', 50000);
-- Update an employee's salary
UPDATE Employee SET emp_salary = 55000 WHERE emp_id = 1;
-- Delete an employee
DELETE FROM Employee WHERE emp_id = 1;
SELECT * FROM Audit_Log;

Output:

Table created.
Table created.
Sequence created.
Trigger created.
1 row(s) inserted.
1 row(s) updated.
1 row(s) deleted.
Result Set 1
LOG_ID	TABLE_NAME	ACTIVITY_TYPE	ACTIVITY_DATE	USER_ID
1	Employee	INSERT	18-AUG-23 12.40.22.286572 PM	APEX_PUBLIC_USER
2	Employee	UPDATE	18-AUG-23 12.40.22.297518 PM	APEX_PUBLIC_USER
3	Employee	DELETE	18-AUG-23 12.40.22.301028 PM	APEX_PUBLIC_USER

Flowchart:

Explanation:

The provided PL/SQL code demonstrates the creation and utilization of a PL/SQL trigger in Oracle Database to establish an audit trail for changes made to an employee table.

The two tables Employee and Audit_Log are involved with the trigger.

A sequence Audit_Log_Seq is established to generate unique log IDs for entries in the Audit_Log table. Each log entry is assigned a unique identifier to ensure traceability.

A trigger named Employee_Audit_Trigger is configured to execute automatically after an INSERT, UPDATE, or DELETE operation is performed on the Employee table. It operates on a row-level basis (FOR EACH ROW) to capture the activity details pertaining to that specific row.

Inside the trigger body, the trigger identifies the type of activity (insert, update, or delete) that triggered its execution and depending on the activity type, the appropriate label is assigned to v_activity_type.

Subsequently, the trigger inserts a new record into the Audit_Log table using the obtained values. The Audit_Log_Seq.NEXTVAL generates a unique log_id. The table name, activity type, current timestamp, and user executing the operation are stored in the corresponding columns.

Previous: Implementing salary change auditing trigger in PL/SQL.
Next: Automating running total calculation with PL/SQL trigger.