🏦 Database Magic: Automating Tasks with Cursor and Trigger ✨

#sql #database #tutorial

Working with databases isn’t just about storing data — it’s about automating how that data behaves.
In this blog, we’ll explore two key SQL features:

🎯 Cursor – to process query results row by row
⚙️ Trigger – to automatically perform actions after data changes

Let’s dive in! 🔍

💼 Part 1: Cursor – Display Employees with Salary > 50,000

A cursor helps you loop through query results one row at a time, perfect for selective data processing.

🧩 Step 1: Create Employee Table & Insert Data

CODE:

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES (1, 'Sophia', 72000.00);
INSERT INTO Employee VALUES (2, 'Ryan', 48000.00);
INSERT INTO Employee VALUES (3, 'Olivia', 83000.00);
INSERT INTO Employee VALUES (4, 'Liam', 39000.00);
INSERT INTO Employee VALUES (5, 'Emma', 65000.00);

✅ This creates an Employee table and fills it with some data.
Notice that some employees have salaries above 50,000.

⚙️ Step 2: Declare and Process the Cursor

We’ll use a cursor to fetch and display names of employees whose salary is greater than 50,000.

CODE:

DELIMITER $$

CREATE PROCEDURE DisplayHighSalaryEmployees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empName VARCHAR(50);
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeName FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO empName;
IF done THEN
LEAVE read_loop;
END IF;
SELECT CONCAT('Employee: ', empName) AS Employee_Name;
END LOOP;
CLOSE emp_cursor;
END $$

DELIMITER ;

CALL DisplayHighSalaryEmployees();

🧾 Output:

✨ Explanation:

DECLARE emp_cursor defines a cursor to select employees with salary > 50,000.
The handler ensures the loop stops when all rows are fetched.
OPEN, FETCH, and CLOSE control the cursor’s lifecycle. Each employee’s name is displayed using a SELECT statement.
When you run the procedure, the cursor loops through all eligible records and prints employee names who earn more than ₹50,000.

⚡ 2️⃣ AFTER INSERT Trigger: Logging Student Registrations

A trigger automatically executes code when a certain event occurs in the database.
Here, we’ll create a trigger that logs every new student registration.

🧩 Step 1: Create Students and Audit Tables

CODE:

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR2(50),
Department VARCHAR2(50) );

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY,
StudentID INT,
Action VARCHAR2(50),
ActionTime TIMESTAMP );

⚙️ Step 2: Create the AFTER INSERT Trigger

This trigger runs automatically after a new student record is inserted.

CODE:

DELIMITER $$

CREATE TRIGGER trg_AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, Action, ActionTime)
VALUES (NEW.StudentID, 'Registered', NOW());
END $$

DELIMITER ;

✅ The :NEW keyword refers to the newly inserted row in the Students table.
✅ SYSTIMESTAMP records the exact time of registration.

🧪 Step 3: Test the Trigger

CODE:

INSERT INTO Students VALUES (201, 'Aarav', 'Computer Science');
INSERT INTO Students VALUES (202, 'Priya', 'Mechanical Engineering');
INSERT INTO Students VALUES (203, 'Karan', 'Electronics');
SELECT * FROM Student_Audit;