In this blog, we’ll explore two important SQL concepts: Cursors and Triggers.
We’ll walk through step-by-step examples:
A cursor that displays employee names with salary greater than 50,000.
An AFTER INSERT trigger that logs student registrations into an audit table.
Part 1: Cursor – Process Cursor with Condition
Problem Statement
We need to create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.
Steps
Create Employee Table
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);Insert Sample Records
INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Alice', 60000),
(2, 'Bob', 48000),
(3, 'Charlie', 75000),
(4, 'David', 45000),
(5, 'Eve', 90000);Create and Use the Cursor
DECLARE @EmpName VARCHAR(50), @Salary DECIMAL(10,2);
DECLARE EmployeeCursor CURSOR FOR
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;
OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor INTO @EmpName, @Salary;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + @EmpName + ' | Salary: ' + CAST(@Salary AS VARCHAR);
FETCH NEXT FROM EmployeeCursor INTO @EmpName, @Salary;
END;
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;
- Output Example Employee: Alice | Salary: 60000 Employee: Charlie | Salary: 75000 Employee: Eve | Salary: 90000
Part 2: Trigger – AFTER INSERT on Student Table
Problem Statement
Whenever a new student is added to the Students table, we want to automatically insert a log entry into the Student_Audit table.
Steps
Create Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Department VARCHAR(50)
);Create Student_Audit Table
CREATE TABLE Student_Audit (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT,
Action VARCHAR(50),
ActionDate DATETIME
);Create the Trigger
CREATE TRIGGER trg_AfterStudentInsert
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (StudentID, Action, ActionDate)
SELECT StudentID, 'INSERT', GETDATE()
FROM inserted;
END;Test the Trigger by Inserting a Student
INSERT INTO Students (StudentID, StudentName, Department)
VALUES (101, 'Rahul', 'Computer Science');Check the Audit Table
SELECT * FROM Student_Audit;
Output Example
AuditID | StudentID | Action | ActionDate
1 | 101 | INSERT | 2025-10-02 10:15:32
Conclusion
With cursors, we can process query results row by row and apply conditions (like salary > 50,000).
With triggers, we can automate actions — in this case, logging every student registration.
Top comments (0)