DEV Community

Boopathy.S
Boopathy.S

Posted on

SQL Cursors and Triggers:-

In this blog, we’ll explore two important SQL concepts: Cursors and Triggers, with hands-on examples.

1️⃣ Using a Cursor with a Condition
A cursor allows you to process rows returned by a query one at a time.
Here, we’ll display the names of employees whose salary is greater than 50,000 from the Employee table.

Step 1: Create Employee Table & Insert Sample Data
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR2(50),
Salary NUMBER
);
INSERT INTO Employee VALUES (101, 'Arjun', 60000);
INSERT INTO Employee VALUES (102, 'Priya', 45000);
INSERT INTO Employee VALUES (103, 'Kiran', 70000);

Step 2: Declare and Use 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;
/



2️⃣ AFTER INSERT Trigger Example
A trigger is a stored procedure that is automatically executed in response to certain events.
Here, we’ll log every new student added to the Students table into a Student_Audit table.

Step 1: Create Students and Student_Audit Tables

CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
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;
/
Step 3: Test the Trigger
INSERT INTO Students (StudentID, StudentName) VALUES ('S01', 'Arjun');
INSERT INTO Students (StudentID, StudentName) VALUES ('S02', 'Priya');

SELECT * FROM Student_Audit;

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pvh481m72hgteqgfzgou.png

Summary
Cursors: Useful to process query results row by row.
Triggers: Automatically execute actions on INSERT, UPDATE, or DELETE events.
Together, they allow you to implement row-level processing and automated logging in SQL.

THANK YOU @santhoshnc sir for guiding me!!!

Top comments (0)