Database Magic: Automating Tasks with Cursor and Trigger✨

🔹 Cursor:

• A cursor in SQL is used to retrieve and process rows one by one from a result set.
• It is mainly used when we want to perform operations row-by-row instead of all at once.

Example Use Case:
Display employee names whose salary > 50,000 by processing each record individually.

🔹 Trigger:

• A trigger is a stored program that is automatically executed in response to certain events (like INSERT, UPDATE, or DELETE) on a table.

Example Use Case:
When a new student is inserted into the Students table, automatically insert a record in the Student_Audit table to log the registration.

1️⃣ Cursor: Process Cursor with Condition

Problem Statement:

We want to iterate through all employees whose salary > 50,000 and print or log their names (or do something per-employee).

CODE:

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

INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Alice', 60000),
(2, 'Bob', 48000),
(3, 'Charlie', 75000),
(4, 'David', 50000),
(5, 'Eve', 90000);

DECLARE @vEmpName VARCHAR(100);
DECLARE @vSalary DECIMAL(18,2);

DECLARE EmployeeCursor CURSOR FOR
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor INTO @vEmpName, @vSalary;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + @vEmpName + ', Salary = ' + CAST(@vSalary AS VARCHAR(20));

FETCH NEXT FROM EmployeeCursor INTO @vEmpName, @vSalary;
END;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Explanation:

• We create a cursor EmployeeCursor selecting EmpName, Salary for those employees whose salary > 50,000.
• OPEN the cursor, then FETCH NEXT row by row into variables.
• @@FETCH_STATUS = 0 means the fetch succeeded.
• Inside the loop, we do whatever per-employee logic (here just PRINT).
• Finally, CLOSE and DEALLOCATE to free resources.

2️⃣ Trigger: AFTER INSERT Trigger on Student → Audit Log

Problem Statement:

Whenever a new student is inserted into the Student table, automatically insert a row into Student_Audit to log that registration (e.g. with timestamp, action, etc.).

CODE:

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Department VARCHAR(100),
YearOfStudy INT,
CGPA DECIMAL(4,2)
);
GO
CREATE TABLE Student_Audit (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT,
StudentName VARCHAR(100),
Action VARCHAR(20),
ActionTime DATETIME
);
GO
CREATE TRIGGER trg_AfterStudentInsert
ON Student
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Student_Audit (StudentID, StudentName, Action, ActionTime)
SELECT i.StudentID, i.StudentName, 'INSERT', GETDATE()
FROM inserted AS i;
END;
GO
INSERT INTO Student (StudentID, StudentName, Department, YearOfStudy, CGPA)
VALUES (101, 'Rahul', 'CSE', 1, 9.2);

INSERT INTO Student (StudentID, StudentName, Department, YearOfStudy, CGPA)
VALUES (102, 'Sita', 'ECE', 2, 8.8);
GO
SELECT * FROM Student_Audit;
GO

Explanation:

• The AFTER INSERT trigger fires after the row(s) are inserted into Student.
• The trigger uses the inserted pseudo-table (available in SQL Server) to get the newly inserted rows.
• For each inserted row, it inserts a corresponding log row in Student_Audit with columns like Action = 'INSERT' and current timestamp.
• So you always have a record of new students inserted.