🚦 Oracle Trigger Tutorial

In this tutorial, you'll learn about Oracle triggers — special stored procedures that automatically execute in response to certain events on a table or view, like INSERT, UPDATE, or DELETE.

📌 What is an Oracle Trigger?

A trigger is a named PL/SQL block stored in the database that automatically runs when a specified event occurs. Triggers help enforce business rules, audit data changes, and maintain integrity.

🧮 Trigger Types

BEFORE triggers: Execute before the triggering event.
AFTER triggers: Execute after the triggering event.
INSTEAD OF triggers: Used on views to replace DML operations.

🧮 Trigger Events

Triggers typically respond to DML events:

INSERT
UPDATE
DELETE

📋 Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} 
{INSERT | UPDATE | DELETE} 
ON table_name
[FOR EACH ROW]
DECLARE
  -- Optional variables
BEGIN
  -- Trigger logic here
END;

📋 Example: BEFORE INSERT Trigger

Before practicing, please alter the employee table and create a salary_audit table.

This trigger sets the created_date to the current date before inserting a new row:

CREATE OR REPLACE TRIGGER trg_before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_date := SYSDATE;
END;

📋 Example: AFTER UPDATE Trigger

This trigger logs salary changes to an audit table:

CREATE OR REPLACE TRIGGER trg_after_update_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

🔄 WHEN Clause

You can add a WHEN clause to conditionally fire triggers:

CREATE OR REPLACE TRIGGER trg_update_salary_high
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > 10000)
BEGIN
  -- Logic for high salary updates
END;

Check a real-time example of the WHEN clause based on the above syntax. Example: Read Here

✅ Summary

Oracle triggers automate actions on DML events.
Types: BEFORE, AFTER, INSTEAD OF.
Can act on INSERT, UPDATE, DELETE.
Use :NEW and :OLD to access row data.
WHEN clause helps filter trigger firing conditions.

Now read the full article on BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE triggers in Oracle Database.

Oracle triggers are powerful tools for enforcing rules and auditing changes automatically.

🚦 Oracle Trigger Tutorial