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:
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.
What is the difficulty level of this exercise?