Cursor & Trigger in SQL with Examples

#database #tutorial #beginners #sql

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;