Mastering Oracle SQL: Cursor and Trigger Examples

Oracle SQL provides powerful features to automate and process data efficiently. In this post, we will explore Cursors and Triggers with practical examples that you can implement right away.

1. Cursors in Oracle SQL

A cursor is a pointer to a result set of a query. It allows you to process rows one by one in PL/SQL. Cursors are especially useful when you want to perform operations conditionally on each row.

Example: Display employees with salary > 50,000
DECLARE
CURSOR emp_cursor IS
SELECT EmployeeName, 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.EmployeeName || ' | Salary: ' || emp_record.Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

✅ This code will output employees whose salary exceeds 50,000.

Key points:

• CURSOR emp_cursor IS … defines the cursor.
• FETCH emp_cursor INTO … retrieves one row at a time.
• emp_cursor%NOTFOUND checks if all rows are processed.

2. Triggers in Oracle SQL

A trigger is a stored PL/SQL block that automatically executes in response to DML events (INSERT, UPDATE, DELETE) on a table. Triggers are ideal for auditing, validation, or automation.

Example: Automatically log new student registrations

We want to track student registrations in a separate Student_Audit table whenever a new student is added.

CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit(AuditID, StudentID, StudentName, RegistrationDate)
VALUES (seq_audit_id.NEXTVAL, :NEW.StudentID, :NEW.StudentName, SYSDATE);
END;
/

How it works:

• AFTER INSERT ON Student → Trigger fires after a new student is added.
• FOR EACH ROW → Executes for every row inserted.
• :NEW → References the new record being inserted.
• Student_Audit automatically logs the student’s details with a timestamp.

3. Benefits of Using Cursors and Triggers

• Cursor Process query results row by row, implement conditional logic, and automate complex tasks.
• Trigger Automatically maintain audit logs, enforce rules, and reduce manual tasks.

Conclusion

Cursors and triggers are essential tools for any Oracle SQL developer. With cursors, you can handle row-level operations efficiently, while triggers help maintain data integrity and automation.

Start experimenting with your own tables and see how these features can save time and reduce errors in real-world applications.

💡 Tip: Combine cursors and triggers to build advanced automation, like automatically processing data after insert or update events.