Cursor +Trigger

#sql #beginners #programming #tutorial

💡 Cursor & Trigger: How They Work in SQL

In this post, we’ll dive into two powerful features in SQL and relational databases: cursors and triggers. You’ll see examples and learn how and when to use them.

🧭 What Are Cursors?

A cursor allows you to process query results row by row. Sometimes you need to iterate through each record individually rather than handling the entire result set at once.

Cursor Use Case Example

Suppose you want to fetch names of employees whose salary is over 50,000 and print them one by one.

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employee VALUES
(1, 'Arjun', 45000),
(2, 'Priya', 60000),
(3, 'Kiran', 75000),
(4, 'Meera', 48000),
(5, 'Rahul', 90000);

Now, the cursor logic:

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;

We declare a cursor that selects employee names with salary over 50,000.

Each loop iteration, FETCH retrieves one row into v_name.

The loop stops when there are no more rows (%NOTFOUND).

We print each employee name.

🔔 What Are Triggers?

A trigger is an automated procedure that fires in response to certain events on a table (INSERT, UPDATE, DELETE). You can use triggers to enforce rules, maintain audit logs, or propagate changes.

Trigger Use Case Example: Logging Insertions

Let’s maintain an audit trail so that whenever a new student is added, we log it into a separate audit table.

-- Base table for students
CREATE TABLE Students (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

-- Audit table to record insert events
CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR(10),
StudentName VARCHAR(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger definition:

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;

AFTER INSERT ON Students means the trigger fires after each insertion.