DEV Community

Mukesh B
Mukesh B

Posted on

SQL Cursor & AFTER INSERT Trigger — Step-by-Step Guide with Example

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!';
Enter fullscreen mode Exit fullscreen mode

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)