PL/SQL Triggers: Syntax, Types, Examples & Usage Guide

Introduction to PL/SQL Triggers

PL/SQL triggers are special stored procedures in Oracle that automatically execute (or “fire”) in response to certain events on a table or view. Unlike procedures or functions, triggers are not called explicitly but are invoked by database events such as INSERT, UPDATE, or DELETE operations.

Triggers are essential for:

Enforcing business rules
Auditing changes
Automatically modifying data
Preventing invalid transactions

All PL/SQL code—whether part of an anonymous block, stored procedure, function, trigger, or package—starts with a block. Even the most complex Oracle application is built from these simple building blocks.

What is a Trigger in PL/SQL?

A PL/SQL trigger is a block of code associated with a DML event (Data Manipulation Language). This block executes automatically when the specified event occurs.

Examples:

A trigger logs every update to employee salary.
It prevents deletion of important rows.
It notifies a user when data is inserted.

Syntax of a PL/SQL Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE} [OF column_name]
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
   -- PL/SQL logic here
END;
/

Components Explained:

Part	Description
`BEFORE` / `AFTER`	Specifies when the trigger fires
`INSERT`, `UPDATE`, `DELETE`	DML operation that activates the trigger
`FOR EACH ROW`	Defines a row-level trigger
`WHEN`	Optional condition that must be met for the trigger to fire
`:OLD`, `:NEW`	Refer to values before and after the change

Types of Triggers in PL/SQL

Type	Description
BEFORE Trigger	Executes before the DML operation
AFTER Trigger	Executes after the DML operation
INSTEAD OF Trigger	Used on views to define trigger logic instead of performing the actual DML
Row-level Trigger	Executes once for each affected row (`FOR EACH ROW`)
Statement-level Trigger	Executes once per SQL statement regardless of how many rows are affected

Example: AFTER UPDATE Trigger in PL/SQL

The following trigger monitors changes to the first_name column in the authors table.

CREATE OR REPLACE TRIGGER author_name_change_trig
AFTER UPDATE OF first_name
ON authors
FOR EACH ROW
WHEN (OLD.first_name != NEW.first_name)
BEGIN
  DBMS_OUTPUT.PUT_LINE('First Name '
    || :OLD.first_name
    || ' has changed to '
    || :NEW.first_name);
END;
/

How to Test It:

SET SERVEROUTPUT ON;
UPDATE authors
SET first_name = 'Ronald'
WHERE first_name = 'Ron';

Output:

First Name Ron has changed to Ronald

This confirms that the trigger executed successfully and printed the change.

Best Practices for Triggers

Keep them simple: Avoid heavy logic to maintain performance.
Avoid nesting too many triggers.
Use proper error handling using EXCEPTION blocks.
Test thoroughly, especially with row-level triggers.

Internal Links for Further Reading

External Reference

Oracle PL/SQL Triggers – Official Documentation

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.