SQL Cursor and Trigger Implementation

In this post, we’ll explore two key SQL programming concepts:
1️⃣ Cursor with condition and 2️⃣ AFTER INSERT Trigger.

Both examples are implemented in Oracle Live SQL.

🌀 1️⃣ Cursor: Display Employees with Salary Greater than 50,000

A cursor is used to process each record returned by a query, one row at a time.

✅ Step 1: Create a Cursor

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;
/

🧠 Explanation:

CURSOR emp_cursor IS → Defines the SQL query.

emp_record emp_cursor%ROWTYPE → Declares a record to store fetched rows.

OPEN, FETCH, CLOSE → Manage cursor operations.

DBMS_OUTPUT.PUT_LINE → Displays each result.

🖥 Output:

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

⚡ 2️⃣ AFTER INSERT Trigger — Student Registration Audit

A trigger automatically performs an action when a specified database event occurs.

✅ Step 2: Create AFTER INSERT Trigger

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;
/

🧠 Explanation:

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

:NEW → Refers to the new row being added.

Student_Audit Table → Logs inserted student records automatically.

✅ Step 3: Test the Trigger

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

🖥 Output:

Trigger TRG_STUDENT_INSERT compiled
1 row inserted

And a new log entry will appear in the Student_Audit table 🎯

🏁 Summary

Feature Description Example

Cursor Used for row-by-row processing Displays employees earning > ₹50,000
Trigger Executes automatically after table events Logs new student registrations

---

💡 Conclusion

Cursors and Triggers are essential for database automation and record handling.
By using them effectively, you can make your SQL programs more powerful, reliable, and dynamic! 🚀
Thank you @santhoshnc sir for guiding and supporting me..