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)