Cursor & Trigger with Examples

#webdev #programming #sql

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 the Employee table (if not already available):

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

Insert some 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

Only employees with salaries greater than 50,000 will be displayed:

Employee: Alice | Salary: 60000
Employee: Charlie | Salary: 75000
Employee: Eve | Salary: 90000

📌 Part 2: Trigger – AFTER INSERT Trigger 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 new student:

INSERT INTO Students (StudentID, StudentName, Department)
VALUES (101, 'Rahul', 'Computer Science');

Check the Audit table:

SELECT * FROM Student_Audit;

Output Example