Introduction
In SQL, Cursors and Triggers are two powerful tools used for automation and data processing.
A Cursor allows you to process rows one-by-one.
A Trigger automatically executes in response to specific table events (like INSERT, UPDATE, or DELETE).
In this tutorial, you’ll learn:
How to create and process a cursor with a condition.
How to create an AFTER INSERT trigger to log student registrations.
Let’s get started! 🚀
🧩 Part 1: Process Cursor with Condition
🎯 Objective:
Display the names of employees whose salary > 50,000 using a cursor.
🏗️ Step 1: Create the Employee Table
CREATE TABLE Employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Department VARCHAR(30),
Salary DECIMAL(10,2)
);
🧮 Step 2: Insert Sample Data
INSERT INTO Employee VALUES
(101, 'Arun Kumar', 'HR', 45000),
(102, 'Meena Devi', 'Finance', 55000),
(103, 'Vishal', 'IT', 75000),
(104, 'Karthik', 'Admin', 48000),
(105, 'Priya', 'IT', 92000);
🧠 Step 3: Create a Cursor to Display Employees with Salary > 50,000
DECLARE
emp_name VARCHAR(50);
emp_salary DECIMAL(10,2);
DECLARE emp_cursor CURSOR FOR
SELECT Emp_Name, Salary
FROM Employee
WHERE Salary > 50000;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO emp_name, emp_salary;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + emp_name + ' | Salary: ' + CAST(emp_salary AS VARCHAR);
FETCH NEXT FROM emp_cursor INTO emp_name, emp_salary;
END;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;
📘 Explanation:
The CURSOR selects only employees with Salary > 50000.
The FETCH statement retrieves each record one by one.
Inside the loop, the result is printed (or displayed via SELECT in MySQL).
✅ Result Example:
Employee: Meena Devi | Salary: 55000.00
Employee: Vishal | Salary: 75000.00
Employee: Priya | Salary: 92000.00
🧩 Part 2: AFTER INSERT Trigger (Student Registration Audit)
🎯 Objective:
Whenever a new student is inserted into the Students table, automatically insert a log entry into the Student_Audit table.
🏗️ Step 1: Create the Students Table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Department VARCHAR(30),
Register_Date DATE
);
🧱 Step 2: Create the Student_Audit Table
CREATE TABLE Student_Audit (
Audit_ID INT IDENTITY(1,1) PRIMARY KEY,
Student_ID INT,
Student_Name VARCHAR(50),
Action VARCHAR(20),
Action_Time DATETIME
);
⚡ Step 3: Create AFTER INSERT Trigger
CREATE TRIGGER trg_AfterInsert_Student
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (Student_ID, Student_Name, Action, Action_Time)
SELECT
i.Student_ID,
i.Student_Name,
'INSERT',
GETDATE()
FROM inserted i;
PRINT 'New student added. Audit log recorded successfully!';
END;
📘 Explanation:
The trigger fires automatically after an insert on the Students table.
The pseudo-table inserted holds the new row being added.
The log entry is recorded in Student_Audit for tracking.
🧮 Step 4: Insert Sample Data
INSERT INTO Students VALUES
(101, 'Arun Kumar', 'CSE', '2025-10-08'),
(102, 'Meena Devi', 'ECE', '2025-10-08');
🧾 Step 5: Verify Audit Logs
SELECT * FROM Student_Audit;
Top comments (0)