PL/SQL After triggers

An AFTER trigger in Oracle PL/SQL is a type of database trigger that is executed automatically after a specified event occurs in a database table. Triggers are special types of stored procedures that are defined to execute automatically in response to certain events on a particular table or view. These events can include INSERT, UPDATE, DELETE, or even DDL (Data Definition Language) statements.

Here, we’ll focus on AFTER triggers in the context of Oracle PL/SQL:

Syntax:

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
DECLARE
  -- Variable declarations
BEGIN
  -- Trigger logic
END;
/

Explanation:

trigger_name: This is the name of the trigger.
AFTER INSERT OR UPDATE OR DELETE: Specifies the event or events that will activate the trigger. In this case, the trigger will fire after an INSERT, UPDATE, or DELETE operation on the specified table.
ON table_name: Specifies the table on which the trigger is defined.
FOR EACH ROW: Indicates that the trigger should be executed once for each row affected by the triggering statement. This is crucial when dealing with row-level operations.

Example:

CREATE OR REPLACE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
  v_old_salary NUMBER;
  v_new_salary NUMBER;
BEGIN
  -- Capture old and new salary values
  v_old_salary := :OLD.salary;
  v_new_salary := :NEW.salary;

  -- Log the salary change
  INSERT INTO salary_audit_log(employee_id, old_salary, new_salary, change_date)
  VALUES (:OLD.employee_id, v_old_salary, v_new_salary, SYSDATE);
END;
/

In this example, the trigger after_employee_update is defined to execute after an update operation on the employees table. The trigger captures the old and new salary values of the affected rows and logs the change in a separate table (salary_audit_log).

Key Points:

Timing: AFTER triggers execute after the triggering event, allowing you to perform actions based on the changes that have occurred.

Use Cases: AFTER triggers are often used for tasks such as audit logging, data validation, or maintaining historical records.

Access to Old and New Values: The :OLD and :NEW qualifiers allow access to the old and new values of the columns affected by the triggering statement.

Avoid Excessive Logic: While triggers can be powerful, it’s important to be mindful of their impact on performance. Excessive or complex logic in triggers can lead to performance issues and should be used judiciously.

In summary, Oracle PL/SQL AFTER triggers provide a mechanism to automatically execute logic in response to specific events, offering a powerful tool for maintaining data integrity and implementing business rules within a database.