Oracle PL/SQL Triggers – Part 1

By /

Introduction

In real-world database systems, data rarely lives in isolation. Every insert, update, or delete often needs validation, auditing, enforcement of business rules, or automation. Oracle PL/SQL Triggers provide a powerful mechanism to react automatically to such data changes.

This blog is a complete, practical guide to Oracle PL/SQL triggers, covering theory, syntax, execution flow, real-time use cases, performance considerations, and troubleshooting—using the HR schema for hands-on examples.

By the end of this article, you’ll not only understand what triggers are, but also when to use them, how to write them safely, and how to avoid common pitfalls.

What Is a Trigger in Oracle PL/SQL?

A trigger is a stored PL/SQL program unit that automatically executes in response to specific database events such as:

  • INSERT
  • UPDATE
  • DELETE
  • Database events like LOGON, STARTUP

Unlike procedures or functions, triggers:

  • Do not require explicit calls
  • Fire implicitly when an event occurs
  • Are tightly coupled with database objects

Why Triggers Exist

Triggers help enforce data integrity and business logic at the database level, ensuring rules are applied regardless of the application accessing the data.

Trigger vs Procedure vs Function

FeatureTriggerProcedureFunction
ExecutionAutomaticManualManual
InvocationDML/Event basedExplicit callExplicit call
Return ValueNoNoYes
Transaction ControlRestrictedAllowedRestricted
Use CaseEnforcement, AuditingBusiness logicCalculations

Types of Triggers in Oracle

Triggers are classified based on timing, event, and level.

Trigger Timing

BEFORE Triggers

  • Fire before the DML operation
  • Commonly used for:
    • Data validation
    • Modifying column values

AFTER Triggers

  • Fire after the DML operation
  • Used for:
    • Auditing
    • Logging changes

INSTEAD OF Triggers

  • Used on views
  • Replace the actual DML operation
  • Essential for non-updatable views

Trigger Events

Triggers can fire on:

  • INSERT
  • UPDATE
  • DELETE
  • Or a combination of them

Trigger Levels

Statement-Level Trigger

  • Executes once per SQL statement
  • Does not use FOR EACH ROW

Row-Level Trigger

  • Executes once per affected row
  • Uses FOR EACH ROW
  • Can access :OLD and :NEW

Basic Trigger Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
timing –- when to fire the trigger
event1 [OR event2 OR event3]
ON object_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW –- default is statement level trigger
WHEN (condition)]]
DECLARE]
BEGIN
... trigger_body –- executable statements
[EXCEPTION . . .]
END [trigger_name];


timing = BEFORE | AFTER | INSTEAD OF 

event = INSERT | DELETE | UPDATE | UPDATE OF column_list

Understanding :OLD and :NEW Pseudorecords

:OLD and :NEW represent row values before and after DML operations.

DML Operation:OLD:NEW
INSERT❌✅
DELETE✅❌
UPDATE✅✅

Demo Setup (Run Once)

DROP TABLE emp_demo_audit;
DROP TABLE emp_demo;

CREATE TABLE emp_demo (
   emp_id      NUMBER PRIMARY KEY,
   emp_name    VARCHAR2(50),
   salary      NUMBER,
   created_on  DATE,
   updated_on  DATE
);

CREATE TABLE emp_demo_audit (
   action_type VARCHAR2(20),
   emp_id      NUMBER,
   action_date DATE
);

1. BEFORE INSERT Trigger – Auto Populate Column

Trigger

CREATE OR REPLACE TRIGGER trg_bi_emp_demo
BEFORE INSERT
ON emp_demo
FOR EACH ROW
BEGIN
   :NEW.created_on := SYSDATE;
END;
/

Test the Trigger

INSERT INTO emp_demo (emp_id, emp_name, salary)
VALUES (101, 'John', 5000);

COMMIT;

Verify the Effect

SELECT emp_id, emp_name, salary, created_on
FROM emp_demo;

What You’ll Observe

  • created_on is automatically populated
  • You didn’t explicitly insert it
  • Confirms BEFORE INSERT trigger execution

2. AFTER INSERT Trigger – Audit Insert Action

Trigger

CREATE OR REPLACE TRIGGER trg_ai_emp_demo
AFTER INSERT
ON emp_demo
FOR EACH ROW
BEGIN
   INSERT INTO emp_demo_audit
   VALUES ('INSERT', :NEW.emp_id, SYSDATE);
END;
/

Test the Trigger

INSERT INTO emp_demo (emp_id, emp_name, salary)
VALUES (102, 'Mary', 6000);

COMMIT;

Verify the Effect

SELECT * FROM emp_demo_audit;

What You’ll Observe

  • A new audit row with INSERT
  • Confirms AFTER INSERT fired successfully

3. BEFORE UPDATE Trigger – Prevent Salary Decrease

Trigger

CREATE OR REPLACE TRIGGER trg_bu_salary
BEFORE UPDATE OF salary
ON emp_demo
FOR EACH ROW
BEGIN
   IF :NEW.salary < :OLD.salary THEN
      RAISE_APPLICATION_ERROR(
         -20001,
         'Salary decrease is not allowed'
      );
   END IF;
END;
/

Test (Invalid Update)

UPDATE emp_demo
SET salary = 4000
WHERE emp_id = 101;

Result

❌ Error raised

ORA-20001: Salary decrease is not allowed

Test (Valid Update)

UPDATE emp_demo
SET salary = 5500
WHERE emp_id = 101;

COMMIT;

Verify the Effect

SELECT emp_id, salary FROM emp_demo WHERE emp_id = 101;

What You’ll Observe

  • Salary decrease blocked
  • Salary increase allowed
  • Demonstrates data validation via trigger

4. AFTER UPDATE Trigger – Track Updates

Trigger

CREATE OR REPLACE TRIGGER trg_au_salary
AFTER UPDATE OF salary
ON emp_demo
FOR EACH ROW
BEGIN
   INSERT INTO emp_demo_audit
   VALUES ('UPDATE', :OLD.emp_id, SYSDATE);
END;
/

Test the Trigger

UPDATE emp_demo
SET salary = 6500
WHERE emp_id = 102;

COMMIT;

Verify the Effect

SELECT * FROM emp_demo_audit
WHERE action_type = 'UPDATE';

What You’ll Observe

  • Audit entry created
  • Shows AFTER UPDATE behavior

5. BEFORE DELETE Trigger – Prevent Delete Based on Condition

Trigger

CREATE OR REPLACE TRIGGER trg_bd_emp_demo
BEFORE DELETE
ON emp_demo
FOR EACH ROW
BEGIN
   IF :OLD.salary > 6000 THEN
      RAISE_APPLICATION_ERROR(
         -20002,
         'Cannot delete high salary employee'
      );
   END IF;
END;
/

Test (Restricted Delete)

DELETE FROM emp_demo WHERE emp_id = 102;

Result

❌ Delete blocked with error

Test (Allowed Delete)

DELETE FROM emp_demo WHERE emp_id = 101;

COMMIT;

Verify the Effect

SELECT * FROM emp_demo;

6. AFTER DELETE Trigger – Audit Deleted Records

Trigger

CREATE OR REPLACE TRIGGER trg_ad_emp_demo
AFTER DELETE
ON emp_demo
FOR EACH ROW
BEGIN
   INSERT INTO emp_demo_audit
   VALUES ('DELETE', :OLD.emp_id, SYSDATE);
END;
/

Test the Trigger

DELETE FROM emp_demo WHERE emp_id = 101;
COMMIT;

Verify the Effect

SELECT * FROM emp_demo_audit
WHERE action_type = 'DELETE';

What You’ll Observe

  • Deleted row is logged
  • Confirms AFTER DELETE execution

7. BEFORE INSERT OR UPDATE Trigger – Maintain Timestamp

Trigger

CREATE OR REPLACE TRIGGER trg_biu_emp_demo
BEFORE INSERT OR UPDATE
ON emp_demo
FOR EACH ROW
BEGIN
   :NEW.updated_on := SYSDATE;
END;
/

Test INSERT

INSERT INTO emp_demo (emp_id, emp_name, salary)
VALUES (103, 'David', 7000);

COMMIT;

Test UPDATE

UPDATE emp_demo
SET salary = 7500
WHERE emp_id = 103;

COMMIT;

Verify the Effect

SELECT emp_id, updated_on
FROM emp_demo
WHERE emp_id = 103;

8. Statement-Level Trigger – Fires Once Per Statement

Trigger

CREATE OR REPLACE TRIGGER trg_stmt_update
AFTER UPDATE
ON emp_demo
BEGIN
   INSERT INTO emp_demo_audit
   VALUES ('BULK UPDATE', NULL, SYSDATE);
END;
/

Test Bulk Update

UPDATE emp_demo
SET salary = salary + 500;

COMMIT;

Verify the Effect

SELECT * FROM emp_demo_audit
WHERE action_type = 'BULK UPDATE';

What You’ll Observe

  • Only one row inserted, even if many rows updated

9. INSTEAD OF INSERT Trigger on View

View

CREATE VIEW emp_demo_view AS
SELECT emp_id, emp_name, salary
FROM emp_demo;

Trigger

CREATE OR REPLACE TRIGGER trg_ioi_emp_view
INSTEAD OF INSERT
ON emp_demo_view
FOR EACH ROW
BEGIN
   INSERT INTO emp_demo (
      emp_id, emp_name, salary, created_on
   )
   VALUES (
      :NEW.emp_id, :NEW.emp_name, :NEW.salary, SYSDATE
   );
END;
/

Test the Trigger

INSERT INTO emp_demo_view
VALUES (104, 'Emma', 8000);

COMMIT;

Verify the Effect

SELECT * FROM emp_demo WHERE emp_id = 104;

What You’ll Observe

  • Insert on view succeeds
  • Data stored in base table
  • Confirms INSTEAD OF trigger behavior

How to Confirm Trigger Execution (Metadata)

SELECT trigger_name, triggering_event, status
FROM user_triggers;

Key Learning Takeaways

  • Triggers run automatically
  • Always verify using SELECT statements
  • Test both valid and invalid scenarios
  • Never assume a trigger works without testing

Practical Example – Salary Validation (HR Schema)

Business Rule

An employee’s salary should never be decreased.

Trigger Code

CREATE OR REPLACE TRIGGER trg_no_salary_decrease
BEFORE UPDATE OF salary
ON hr.employees
FOR EACH ROW
BEGIN
   IF :NEW.salary < :OLD.salary THEN
      RAISE_APPLICATION_ERROR(
         -20001,
         'Salary decrease is not allowed'
      );
   END IF;
END;
/

Explanation

  • Fires before update
  • Checks old and new salary values
  • Prevents invalid business operation at DB level

Trigger Execution Order

When a DML statement executes, Oracle follows this order:

  1. BEFORE STATEMENT trigger
  2. BEFORE EACH ROW trigger
  3. DML execution
  4. AFTER EACH ROW trigger
  5. AFTER STATEMENT trigger

Understanding this order is critical for debugging complex trigger logic.

Multiple Triggers on the Same Table

If multiple triggers exist:

  • Oracle does not guarantee execution order
  • Use compound triggers to control flow
  • Avoid interdependent triggers

Disclaimer

This blog is intended for educational purposes only.
The examples are based on the Oracle HR sample schema and simplified business rules.
Always validate trigger logic in development and testing environments before applying it to production systems.
Trigger usage should be carefully reviewed for performance, maintainability, and scalability.

Scroll to Top