Mastering SQL: A Practical Guide to Cursors and Triggers in Oracle

Database management is a core skill for any developer, and two of the most powerful tools in SQL are cursors and triggers. While they serve different purposes, both allow for fine-grained control and automation within your database. In this post, we'll walk through a practical example of each using Oracle Live SQL.

Part 1: Cursors – Processing a Result Set Row by Row
A cursor is a database object that enables you to iterate through the records of a query's result set one at a time. This is particularly useful for performing actions that can't be done with a single SQL statement, like conditional processing or calling a function for each row.

The Task: We want to create a cursor that finds and displays the names of all employees whose salary is greater than $50,000.

Step 1: Create the Employee Table
First, we need a table to hold our employee data. Here's the SQL to create the Employee table and populate it with some sample data.

SQL

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR2(100),
Salary NUMBER(10, 2)
);

INSERT INTO Employee VALUES (1, 'Ravi', 65000.00);
INSERT INTO Employee VALUES (2, 'Ram', 48000.00);
INSERT INTO Employee VALUES (3, 'Nivas', 72000.00);
INSERT INTO Employee VALUES (4, 'Guhan', 51000.00);

COMMIT;

Step 2: Use a PL/SQL Block to Process the Cursor
Now, we'll write a PL/SQL block. We'll declare a cursor that selects employees with a salary over $50,000, open it, loop through the results, and print the employee's name and salary.

SQL

SET SERVEROUTPUT ON;

DECLARE
v_employee_name Employee.EmployeeName%TYPE;
v_salary Employee.Salary%TYPE;

CURSOR c_employee IS
SELECT EmployeeName, Salary
FROM Employee
WHERE Salary > 50000;

BEGIN
OPEN c_employee;

LOOP
    FETCH c_employee INTO v_employee_name, v_salary;

    EXIT WHEN c_employee%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name || ' | Salary: ' || v_salary);
END LOOP;

CLOSE c_employee;

END;
/
As you can see from the output, the code successfully identified and printed the employees who meet the salary condition.

Part 2: Triggers – Automating Database Events
A trigger is a stored procedure that automatically runs when a specific event occurs on a table, such as an INSERT, UPDATE, or DELETE. Triggers are perfect for tasks like maintaining audit logs or enforcing business rules.

The Task: We want to create an AFTER INSERT trigger on our Students table. Whenever a new student is added, a log entry should be automatically inserted into a separate Student_Audit table.

Step 1: Create the Students and Student_Audit Tables
We need two tables: Students to store the student data and Student_Audit to store the log entries.

SQL

-- Create the Students table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR2(100),
RegistrationDate DATE
);

-- Create the Student_Audit table
CREATE TABLE Student_Audit (
AuditID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
StudentID INT,
StudentName VARCHAR2(100),
ActionType VARCHAR2(20),
ActionDate TIMESTAMP
);

Step 2: Create the AFTER INSERT Trigger
The following trigger uses the special :NEW virtual record to access the values of the row that was just inserted and automatically logs them into the Student_Audit table.

SQL

CREATE OR REPLACE TRIGGER trg_Student_Audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (
StudentID,
StudentName,
ActionType,
ActionDate
) VALUES (
:NEW.StudentID,
:NEW.StudentName,
'INSERT',
SYSTIMESTAMP
);
END;
/
Step 3: Test the Trigger by Inserting Data
Now, let's insert a few student records. For each INSERT statement we run, the trigger will automatically fire.

SQL

-- Insert the new student records
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (101, 'Ravi', SYSDATE);
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (102, 'Ram', SYSDATE);
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (103, 'Nivas', SYSDATE);
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (104, 'Guhan', SYSDATE);

Finally, we'll query the Student_Audit table to confirm that all four INSERT actions were logged.

SQL

SELECT * FROM Student_Audit;
The output clearly shows that the trigger worked perfectly. Each new student was logged with their StudentID, StudentName, and the time of the insertion.

Conclusion
As you can see, cursors and triggers are incredibly useful for handling specific data-processing tasks and automating operations. They give you the power to go beyond standard SQL statements and build a more robust and responsive database system.