Cursor + Trigger

🚀 Using Cursor and Trigger in SQL (with Examples)
In SQL, cursors and triggers are powerful features that allow you to process queries row by row and automate actions when data changes. Let’s learn how to use them with practical examples.

🔹 Cursor Example: Employees with Salary > 50,000
We want to display employee names whose salary is greater than 50,000 using a cursor.

Step 1: Employee Table
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);

-- Sample Data
INSERT INTO Employee VALUES (1, 'John', 60000);
INSERT INTO Employee VALUES (2, 'Alice', 45000);
INSERT INTO Employee VALUES (3, 'Bob', 75000);
INSERT INTO Employee VALUES (4, 'Emma', 52000);

Step 2: Cursor with Condition

SET SERVEROUTPUT ON;

DECLARE
v_EmpName Employee.EmpName%TYPE; -- variable to hold employee name
CURSOR emp_cursor IS
SELECT EmpName FROM Employee WHERE Salary > 50000;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_EmpName;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_EmpName);
END LOOP;
CLOSE emp_cursor;
END;
/

🔹 Trigger Example: AFTER INSERT on Student Table
Whenever a new student is added, we want to log it automatically into an audit table.

Step 1: Create Student & Audit Tables

CREATE TABLE Students ( StudentID INT PRIMARY KEY, StudentName VARCHAR(50), Department VARCHAR(50) );

CREATE TABLE Student_Audit ( AuditID INT IDENTITY(1,1) PRIMARY KEY, StudentID INT, ActionTime DATETIME, ActionPerformed VARCHAR(50) );

Step 2: AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_AfterInsert_Student
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, ActionTime, ActionPerformed)
VALUES (:NEW.StudentID, SYSTIMESTAMP, 'INSERT');
END;
/

Step 3: Test the Trigger

INSERT INTO Students VALUES (1, 'Mike', 'CSE');
INSERT INTO Students VALUES (2, 'Sophia', 'IT');

SELECT * FROM Student_Audit;

🎯 Conclusion
Cursor lets us fetch data row by row and apply conditions.
Trigger automates actions after an event (INSERT/UPDATE/DELETE).
Together, they help in data processing and auditing in real-time.