Know the Triggers in Oracle PL/SQL : An Detailed Guide

Introduction:

Triggers are an essential part of PL/SQL (Procedural Language/Structured Query Language) and provide a way to automatically execute a set of statements in response to specific events occurring in a database. This blog post aims to provide a comprehensive understanding of triggers in Oracle PL/SQL, covering their types, components, and practical examples.

Types of Triggers:

1. DML Triggers: These triggers fire in response to Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. They are further classified into row-level triggers and statement-level triggers.
2. DDL Triggers: These triggers fire in response to Data Definition Language (DDL) statements such as CREATE, ALTER, and DROP.
3. System Triggers: These triggers are fired in response to system-level events like database startup or shutdown.

Components of a Trigger:

1. Trigger Name: A unique name that identifies the trigger.
2. Trigger Event: The event that fires the trigger, such as INSERT, UPDATE, DELETE, or a specific DDL statement.
3. Trigger Type: Indicates whether the trigger is a row-level trigger or a statement-level trigger.
4. Trigger Timing: Specifies when the trigger should fire—either before or after the event.
5. Trigger Body: The set of PL/SQL statements that execute when the trigger fires.

Code Examples Oracle PL/SQL:

1. Creating a Simple Row-Level Trigger:

CREATE OR REPLACE TRIGGER employee_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, action, username, timestamp)
  VALUES ('employees', 'INSERT', USER, SYSDATE);
END;
/

In the above example, a row-level trigger named “employee_after_insert” is created. It fires after each row is inserted into the “employees” table. It captures the action details and inserts a record into the “audit_log” table.

2. Creating a Statement-Level Trigger:

CREATE OR REPLACE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
  UPDATE employee_statistics
  SET total_salary = total_salary - :old.salary + :new.salary
  WHERE emp_id = :old.emp_id;
END;
/

Here, a statement-level trigger named “salary_update_trigger” is defined. It fires after the “salary” column is updated in the “employees” table. It calculates the change in the total salary and updates the corresponding record in the “employee_statistics” table.

Conclusion of Triggers in Oracle PL/SQL

Triggers in PL/SQL provide a powerful mechanism to automate actions in response to specific events occurring in a database. Whether you need to capture audit logs, maintain statistics, or enforce complex business rules, triggers can be effectively employed. Understanding the types, components, and syntax of triggers is essential for harnessing their full potential. By utilizing the provided code examples as a starting point, you can explore and leverage triggers in your PL/SQL applications.