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!';
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;
DEV Community Surveys ❤️
Please help our partners improve AI tooling by completing this very short survey:
Google AI User Experience Survey
Please choose the point that best applies to you for Google AI.
(1) It is very difficult to navigate their different AI offerings
(7) It is very easy to navigate their different AI offerings
← Very difficult to navigate
Very easy to navigate →
1
2
3
4
5
6
7
← Previous
Question 1 of 6
Next →
None of your personal information will be shared. Once you complete the survey you will not be prompted for completion anymore.
Thank you!! ❤️
Top comments (0)