DEV Community

Cover image for Cursor + Trigger in SQL: A Beginner-Friendly Guide
Deepana
Deepana

Posted on

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.

Top comments (0)