DEV Community

Nethra Loganathan
Nethra Loganathan

Posted on

Cursor & Trigger in SQL with Examples

In this blog, we’ll explore two important SQL concepts: Cursors and Triggers.
We’ll walk through step-by-step examples:

A cursor that displays employee names with salary greater than 50,000.

An AFTER INSERT trigger that logs student registrations into an audit table.

Part 1: Cursor – Process Cursor with Condition
Problem Statement

We need to create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

Steps

  1. Create Employee Table
    CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    Salary DECIMAL(10,2)
    );

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

  3. Create and Use the Cursor
    DECLARE @EmpName VARCHAR(50), @Salary DECIMAL(10,2);

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

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor INTO @EmpName, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + @EmpName + ' | Salary: ' + CAST(@Salary AS VARCHAR);
FETCH NEXT FROM EmployeeCursor INTO @EmpName, @Salary;
END;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

  1. Output Example Employee: Alice | Salary: 60000 Employee: Charlie | Salary: 75000 Employee: Eve | Salary: 90000

Part 2: Trigger – AFTER INSERT on Student Table
Problem Statement

Whenever a new student is added to the Students table, we want to automatically insert a log entry into the Student_Audit table.

Steps

  1. Create Students Table
    CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50),
    Department VARCHAR(50)
    );

  2. Create Student_Audit Table
    CREATE TABLE Student_Audit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID INT,
    Action VARCHAR(50),
    ActionDate DATETIME
    );

  3. Create the Trigger
    CREATE TRIGGER trg_AfterStudentInsert
    ON Students
    AFTER INSERT
    AS
    BEGIN
    INSERT INTO Student_Audit (StudentID, Action, ActionDate)
    SELECT StudentID, 'INSERT', GETDATE()
    FROM inserted;
    END;

  4. Test the Trigger by Inserting a Student
    INSERT INTO Students (StudentID, StudentName, Department)
    VALUES (101, 'Rahul', 'Computer Science');

  5. Check the Audit Table
    SELECT * FROM Student_Audit;

Output Example
AuditID | StudentID | Action | ActionDate

1 | 101 | INSERT | 2025-10-02 10:15:32

Conclusion

With cursors, we can process query results row by row and apply conditions (like salary > 50,000).

With triggers, we can automate actions — in this case, logging every student registration.

Top comments (0)