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)