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

Triggers can be categorized based on timing (before/after), event (insert/update/delete), and level (row or statement). Here's an overview of the types of triggers:

1. Based on Timing

BEFORE Trigger: Executes before the triggering DML (Data Manipulation Language) operation (INSERT/UPDATE/DELETE) occurs. These are typically used for validation or modification of data before it is committed.

AFTER Trigger: Executes after the DML operation. This is often used for logging changes, enforcing business rules, or cascading updates.

2. Based on the Event

Triggers can fire on the following DML events:

INSERT: Fired when a row is inserted into a table.

UPDATE: Fired when a row is updated in a table.

DELETE: Fired when a row is deleted from a table.

3. Based on the Level

Row-level Trigger: Fires once for each row affected by the DML operation. This is useful when you need to handle each row individually, such as setting values for certain columns.

Statement-level Trigger: Fires once for the entire DML operation, regardless of how many rows are affected. This is generally used when you want to perform an action that isn't specific to individual rows, like writing to an audit log.

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.