PL/SQL Triggers: Employee Salary Limit Enforcement & Audit Logging

PL/SQL Trigger Exercise: Employee Salary Management

Consider a database table: Employees(EmployeeID, Name, Department, Salary). You are required to implement the following triggers:

Tasks

1. Salary Limit Enforcement:
   • Write a trigger that prevents any employee’s salary from being updated to a value greater than 200,000.
2. Salary Change Logging:
   • Write a trigger that automatically inserts a record into a separate table: Salary_Log(EmployeeID, Old_Salary, New_Salary, Change_Date) whenever an employee’s salary is updated.
3. Trigger Type Analysis:
   • For each trigger, identify the type (BEFORE/AFTER, INSERT/UPDATE/DELETE).
   • Justify why the chosen trigger type is appropriate for the given scenario.

Instructions

• Use PL/SQL syntax for trigger creation.
• Include comments in your code to explain the logic.
• Highlight how each trigger ensures data integrity and auditability.

PL/SQL employee salary trigger, Oracle trigger tutorial, BEFORE UPDATE trigger, AFTER UPDATE trigger, salary limit enforcement trigger, salary audit log trigger, Employee Salary_Log table, database trigger example, Oracle PL/SQL tutorial, SQL triggers for data integrity

PL/SQL Triggers: Employee Salary Limit & Audit Logging

The following PL/SQL triggers ensure salary limit enforcement and audit logging for the Employees table.

1. BEFORE UPDATE Trigger: Enforce Salary Limit

CREATE OR REPLACE TRIGGER trg_salary_limit
BEFORE UPDATE OF Salary ON Employees
FOR EACH ROW
BEGIN
  -- Prevent salary from exceeding 200,000
  IF :NEW.Salary > 200000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot exceed 200,000.');
  END IF;
END;
/
  

Trigger Type: BEFORE UPDATE
Justification: The trigger checks the new salary before it is applied. This prevents invalid data from being written, ensuring data integrity.

2. AFTER UPDATE Trigger: Log Salary Changes

CREATE OR REPLACE TRIGGER trg_salary_log
AFTER UPDATE OF Salary ON Employees
FOR EACH ROW
BEGIN
  -- Insert old and new salary into Salary_Log table
  INSERT INTO Salary_Log(EmployeeID, Old_Salary, New_Salary, Change_Date)
  VALUES (:OLD.EmployeeID, :OLD.Salary, :NEW.Salary, SYSDATE);
END;
/
  

Trigger Type: AFTER UPDATE
Justification: The trigger executes after the salary update is successfully applied, ensuring that only valid changes are logged in the audit table.

Best Practices & Notes

• Use BEFORE triggers for validation to prevent invalid data modifications.
• Use AFTER triggers for logging and auditing, ensuring that only successful changes are recorded.
• Always include RAISE_APPLICATION_ERROR to notify users of constraint violations.
• Maintain a separate Salary_Log table to track all changes for compliance and auditing purposes.

PL/SQL salary limit trigger, BEFORE UPDATE trigger example, AFTER UPDATE salary log, Employees table trigger, Oracle PL/SQL tutorial, salary audit logging, data integrity triggers, Oracle database triggers, employee salary enforcement, SQL trigger examples