Cursor + Trigger

#tutorial #database #beginners #sql

In this post, we'll explore two powerful tools in relational databases:

Cursors — for iterating row-by-row over query results

Triggers — for automating logic in response to data changes

We’ll cover what they are, how to use them, and example use-cases.

What Is a Cursor?

A cursor is a database object that lets you process query results row by row, instead of processing the entire result set at once. This is useful when you need to run procedural logic for each row, e.g. for validations, formatting, or complex computations.

Employees Table
CR EATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employee VALUES
(1, 'Arjun', 45000),
(2, 'Priya', 60000),
(3, 'Kiran', 75000),
(4, 'Meera', 48000),
(5, 'Rahul', 90000);

Output

Cursor That Fetches High-Salary Employees

Let’s say you want to print out names of employees whose salary exceeds 50,000.

CODE:

DECLARE
CURSOR high_salary_cursor IS
SELECT EmpName
FROM Employee
WHERE Salary > 50000;

v_name Employee.EmpName%TYPE;
BEGIN
OPEN high_salary_cursor;
LOOP
FETCH high_salary_cursor INTO v_name;
EXIT WHEN high_salary_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE high_salary_cursor;
END;

What Is a Trigger?

A trigger is a named procedure that fires automatically in response to certain events on a table — INSERT, UPDATE, or DELETE. Triggers are useful when you want to enforce rules, maintain audit logs, or replicate changes.
Students + Audit Table

Create Student and Audit Tables
CREATE TABLE Studt (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR(10),
StudentName VARCHAR(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TRIGGER student_insert_audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName)
VALUES (:NEW.StudentID, :NEW.StudentName);
END;

Insert Data into Studt
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

SELECT * FROM Student_Audit;

When & Why to Use Cursors and Triggers
Cursor
Use Case: Row-by-row logic (e.g. complex processing per record)
Caution: Can be slow on large datasets — try set-based SQL where possible.
Trigger
Use case: Automatic enforcement (audit logs, validations, replication)
Caution: Make triggers simple and predictable, debugging can get tricky

Use cursors when procedural logic is hard or impossible to express purely with SQL.

Use triggers to ensure data consistency, enforce business rules, or maintain history without manual invocation.