Cursor + Trigger

#database #tooling #learning #sql

Introduction

In this blog we will explore two powerful SQL concepts, Cursors and Triggers. These tools help you handle row-by-row processing and automate actions in response to data changes, respectively.

A cursor allows you to fetch and process rows returned by a query one at a time, which is useful for row-level operations that cannot be done in a single SQL statement.

CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT
);

INSERT INTO Employee VALUES(1, 'John Doe', 60000);
INSERT INTO Employee VALUES(2, 'Jane Brown', 45000);
INSERT INTO Employee VALUES(3, 'Jim Brown', 52000);
INSERT INTO Employee VALUES(4, 'Lisa White', 48000);
INSERT INTO Employee VALUES(5, 'Mike Black', 75000);

Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

DECLARE emp_cursor CURSOR FOR
SELECT emp_name FROM Employee WHERE salary > 50000;

DECLARE @emp_name VARCHAR(50);

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @emp_name;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @emp_name; -- or use SELECT in some systems
FETCH NEXT FROM emp_cursor INTO @emp_name;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Trigger
AFTER INSERT Trigger (Student Table)
Whenever a new student is added to the Students table, we want to automatically insert a log entry into the Student_Audit table to keep track of the registration.