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

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!! ❤️