Databases are the backbone of modern applications, enabling efficient data storage, retrieval, and powerful manipulation. When working with Oracle SQL, two powerful features—cursors and triggers—help automate tasks and process data row by row. In this post, I’ll explain both concepts, show you practical examples, and share screenshots of implementation using Oracle Live SQL.
What is a Cursor?
A cursor in SQL acts like a pointer that allows row-by-row processing of result sets. It’s especially useful when you need to perform operations on each row individually, which standard SQL queries can’t accomplish directly.
Example: Printing Employees with High Salaries
Suppose we have an Employee table with fields: EmpID, EmpName, and Salary. Our goal is to print names of employees who earn more than 50,000.
PL/SQL Block:
sql
DECLARE
CURSOR high_salary_cursor IS
SELECT EmpName FROM Employee WHERE Salary > 50000;
v_name Employee.EmpName%TYPE;
BEGIN
OPEN high_salary_cursor;
LOOP
FETCH high_salary_cursor INTO v_name;
EXIT WHEN high_salary_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE high_salary_cursor;
END;
This script fetches names one by one and prints them if their salary is above 50,000. In my test run, employees Priya, Kiran, and Rahul were displayed.
What is a Trigger?
A trigger is a special type of stored procedure that automatically executes (or “fires”) in response to certain events on a database table or view, such as insert, update, or delete. Triggers are great for enforcing data integrity, logging actions, or handling business logic.
Example: Auditing Student Records on Insert
Let’s say we want to track all inserts into a Students table. We’ll do this by creating an audit table and a trigger.
Step1: Create Audit Table
sql
CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR(10),
StudentName VARCHAR(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step2: Create the Trigger
sql
CREATE OR REPLACE TRIGGER student_insert_audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName)
VALUES (:NEW.StudentID, :NEW.StudentName);
END;
/
Now, every time a new student is inserted into the Students table, their details are copied into Student_Audit with a
timestamp—no manual intervention needed!
Screenshots
List of employees and their salary data (used for cursor demo)
PL/SQL block (cursor) with successful output
Table creation and row insert examples
Student audit table contents after trigger fires
Why Use Cursors and Triggers?
Cursors: Required for row-by-row logic, batch processing, or reporting use cases.
Triggers: Ideal for automatic logging, enforcing rules, and enabling real-time reactions to data changes.
Final Thoughts:
Mastering cursors and triggers unlocks new automation possibilities in your SQL workflow. Try these examples in Oracle Live SQL, and expand the concepts to fit your own applications. If you have questions, drop them in the comments!
Happy querying and automating!
Top comments (0)