DEV Community

Cover image for Mastering SQL Cursors & Triggers in Oracle
Ramya
Ramya

Posted on

Mastering SQL Cursors & Triggers in Oracle

In this post, we’ll dive into two essential SQL concepts:
1️⃣ Using a Cursor with a condition
2️⃣ Implementing an AFTER INSERT Trigger

All examples are built in Oracle Live SQL.

🌀 1️⃣ Cursor: List Employees with Salary > 50,000

A cursor allows you to process query results row by row, giving you precise control over each record.

Example:

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

emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.EmpName ||
', Salary: ' || emp_record.Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

How it works:

CURSOR emp_cursor IS → Defines the query.

emp_record emp_cursor%ROWTYPE → Creates a record to store each fetched row.

OPEN, FETCH, CLOSE → Manage cursor operations.

DBMS_OUTPUT.PUT_LINE → Prints each result.

Output:

Employee: Arjun, Salary: 60000
Employee: Kiran, Salary: 80000

⚡ 2️⃣ AFTER INSERT Trigger: Student Registration Audit

A trigger automatically runs in response to specific table events.

Example:

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

How it works:

AFTER INSERT ON Students → Trigger fires after a new record is inserted.

:NEW → References the newly inserted row.

Student_Audit → Automatically logs new student entries.

Test it:

INSERT INTO Students (StudentID, StudentName)
VALUES ('S01', 'Arjun');

Output:

Trigger TRG_STUDENT_INSERT compiled
1 row inserted

✅ A new log entry is now in Student_Audit.

🏁 Summary

In this post, we explored SQL Cursors and AFTER INSERT Triggers in Oracle:

Cursor: Processes query results row by row, allowing precise control over each record. We used it to display employees earning more than ₹50,000.

Trigger: Automatically executes when a specified database event occurs. We implemented an AFTER INSERT trigger to log new student registrations in an audit table.

🎯 Key Takeaways

Feature Purpose Example

Cursor Row-by-row processing of query results List employees with Salary > ₹50,000
Trigger Automatic action after table events Log new student registrations into Student_Audit

Top comments (0)