Cursor + Trigger in SQL: A Beginner-Friendly Guide

Cursor + Trigger in SQL

In this blog, we will clearly understand Cursor and Trigger with step-by-step explanation and examples.

🔹 Part 1: Cursor Example

📌 What is a Cursor?

• A cursor is like a pointer which allows us to fetch rows one by one from a query result.
• Useful when we want to process each record individually.

📝 Task

Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

💻 SQL Code

DECLARE
CURSOR emp_cursor IS
SELECT EmployeeName
FROM Employee
WHERE Salary > 50000;
v_emp_name Employee.EmployeeName%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
END LOOP;
CLOSE emp_cursor;
END;
/

🔎 Explanation (Step by Step)

DECLARE Cursor → We define a cursor to select employees with salary > 50,000.
Variable → v_emp_name stores each fetched employee name.
OPEN Cursor → Cursor execution starts.
LOOP + FETCH → Each row is fetched and displayed.
EXIT WHEN NOTFOUND → Loop stops when no rows left.
CLOSE Cursor → Cursor is closed to release memory.

🔹 Part 2: Trigger Example

📌 What is a Trigger?

A trigger is a stored program that is automatically executed when a specific event (INSERT, UPDATE, DELETE) occurs on a table.

📝 Task

Whenever a new student is added to the Students table, automatically insert a log entry into the Student_Audit table.

💻 SQL Code

CREATE OR REPLACE TRIGGER trg_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit(StudentID, Action, ActionDate)
VALUES(:NEW.StudentID, 'INSERT', SYSDATE);
END;
/
INSERT INTO Students (StudentID, StudentName)
VALUES (102, 'Divya');
COMMIT;
SELECT * FROM Student_Audit;

📌 Explanation:

This trigger fires AFTER INSERT on Students.

For each new student, a record is automatically added in Student_Audit with date & action type.

✨ That’s it!
With these two examples, we learned:
How to use a Cursor with condition.
How to create an AFTER INSERT Trigger to maintain audit logs.