PL/SQL Triggers

PL/SQL Triggers:

BEFORE and AFTER with Examples

A trigger in PL/SQL is a special type of stored procedure that is executed automatically in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE operations.

.
.
.

Types of Triggers in PL/SQL:

1. BEFORE Trigger: Executes before the triggering event occurs (e.g., before an INSERT, UPDATE, or DELETE statement).
2. AFTER Trigger: Executes after the triggering event has occurred.

Example Table:

Let's consider a simple employees table.

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_salary NUMBER(10, 2),
    created_date DATE,
    updated_date DATE
);

.
.
.

1. BEFORE Trigger Example:

This trigger will automatically set the created_date before a new employee is inserted into the employees table.

Trigger Definition:

CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_date := SYSDATE;  
-- Automatically set created_date before insertion
END;

Explanation:

• BEFORE INSERT: The trigger fires before a row is inserted into the employees table.
• :NEW.created_date: Refers to the new value being inserted into the created_date column. This trigger sets the value to the current system date (SYSDATE) before the insertion happens.

Usage:

INSERT INTO employees (emp_id, emp_name, emp_salary)
VALUES (101, 'John Doe', 50000);

After inserting, the created_date will automatically be set to the current date and time.

.
.
.

2. AFTER Trigger Example:

This trigger will automatically update the updated_date after an employee’s salary is updated in the employees table.

Trigger Definition:

CREATE OR REPLACE TRIGGER after_update_employee
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.updated_date := SYSDATE;  
-- Automatically set updated_date after an update
END;

Explanation:

• AFTER UPDATE: The trigger fires after a row is updated in the employees table.
• :NEW.updated_date: This sets the updated_date to the current date and time after the update happens.

Usage:

UPDATE employees
SET emp_salary = 55000
WHERE emp_id = 101;

After updating the employee’s salary, the updated_date will automatically be set to the current date and time.

.
.
.

Key Points:

BEFORE Triggers: Useful for setting default values, validations, and preparing data before an event.

AFTER Triggers: Used for actions like logging changes, updating audit tables, or modifying related data after the event has occurred.

Triggers can help in automating tasks and enforcing business rules, but they should be used carefully to avoid performance issues.