1.
Introduction
A trigger is a stored program that is automatically executed (or fired) when a specific event
occurs in the database.
Triggers help enforce rules, automate tasks, maintain logs, and ensure data integrity.
A trigger can fire in response to the following types of events:
• DML statements: INSERT, UPDATE, DELETE
• DDL statements: CREATE, ALTER, DROP
• Database operations: LOGON, LOGOFF, STARTUP, SHUTDOWN, SERVERERROR
Triggers may be created on a table, view, schema, or the entire database, depending on the
requirement.
2. Benefits of Using Triggers
Triggers are commonly used for:
1. Automatically generating derived column values
2. Enforcing referential integrity
3. Recording events or access logs
4. Auditing database activity
5. Replicating table data synchronously
6. Implementing security rules
7. Preventing invalid or unauthorized transactions
Syntax for Creating a Trigger
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
declaration_statements
BEGIN
executable_statements
EXCEPTION
exception_handling_statements
END;
Explanation of Key Clauses
CREATE [OR REPLACE] TRIGGER trigger_name
Creates a new trigger or replaces an existing one.
BEFORE | AFTER | INSTEAD OF
Specifies when the trigger should execute.
INSTEAD OF is used only for triggers on views.
INSERT | UPDATE | DELETE
Specifies the DML operations that activate the trigger.
OF column_name
Used with UPDATE triggers to specify the columns being monitored.
ON table_name
The table or view on which the trigger is defined.
REFERENCING OLD AS o NEW AS n
Provides access to old and new row values during DML operations.
FOR EACH ROW
Indicates a row-level trigger. Without this, it is a statement-level trigger.
WHEN (condition)
Applies a condition for row-level triggers.
CREATE TABLE CUSTOMERS (
ID NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(50),
AGE NUMBER(3),
ADDRESS VARCHAR2(100),
SALARY NUMBER(10,2)
);
insert 3 records
Example: Creating a Row-Level Trigger
The following trigger fires whenever a row is INSERTED, UPDATED, or DELETED in
the CUSTOMERS table.
It displays the old salary, new salary, and their difference.
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN ([Link] > 0)
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :[Link] - :[Link];
dbms_output.put_line('Old salary: ' || :[Link]);
dbms_output.put_line('New salary: ' || :[Link]);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
Important Notes
• :OLD and :NEW are only available for row-level triggers.
• Use AFTER triggers when the trigger needs to query or modify the same table.
• Triggers may be written for specific operations (e.g., BEFORE DELETE).
Trigger Execution
INSERT Operation
INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)VALUES (7, 'Kriti', 22, 'HP',
7500.00);
UPDATE Operation
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
Example: Automatically Track When a User Record Is Updated
Create Main Table
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
updated_at TIMESTAMP
);
Create Trigger
This trigger automatically updates the updated_at field whenever the
user record is modified.
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END;
Insert Sample Data
INSERT INTO users (id, name, email) VALUES (1, 'Amit',
'amit@[Link]');
Update a Record
UPDATE users SET email = 'amit_new@[Link]' WHERE id = 1;
id name email updated_at
2025-10-27 12:45:23 (current
1 Amit amit_new@[Link]
timestamp auto-updated)
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
FOR EACH ROW
BEGIN
END;
In this explanation:
• trigger_name: The name of the trigger to be created
• BEFORE | AFTER: Specifies whether the trigger is fired before
or after the triggering event (INSERT, UPDATE, DELETE).
• {INSERT | UPDATE | DELETE}: Specifies the operation that will
activate the trigger.
• table_name: The name of the table the trigger is associated
with.
• FOR EACH ROW: Indicates that the trigger is row-level,
meaning it executes once for each affected row.
• trigger_body: The SQL statements to be executed when the
trigger is fired.