Cursors and Triggers

In this tutorial, we’ll explore how to use cursors and triggers in MySQL 8.0.

We will cover:

1. Using a cursor to fetch employees with salary greater than 50,000.
2. Creating an AFTER INSERT trigger to automatically log student registrations. The guide includes ready-to-paste SQL, expected outputs, screenshots, and troubleshooting tips.

Step 1 — Create a fresh database
DROP DATABASE IF EXISTS demo_db;
CREATE DATABASE demo_db;
USE demo_db;

Screenshot:

Step 2 — Create Employee table and insert sample rows
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);
INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Arjun', 60000),
(2, 'Priya', 45000),
(3, 'Kiran', 75000),
(4, 'Meera', 30000);

Screenshot:

Step 3 — Create stored procedure with a cursor
We’ll create ShowHighSalaryEmployees() to display employees with Salary > 50,000 row by row.

DROP PROCEDURE IF EXISTS ShowHighSalaryEmployees;
DELIMITER $$
CREATE PROCEDURE ShowHighSalaryEmployees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empName VARCHAR(50);
DECLARE empSalary DECIMAL(10,2);
DECLARE cur CURSOR FOR
SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO empName, empSalary;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SELECT empName AS Employee, empSalary AS Salary;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;

Screenshot:

Step 4 — Call the procedure
CALL ShowHighSalaryEmployees();

Expected Output:
+----------+---------+
| Employee | Salary |
+----------+---------+
| Arjun | 60000.0 |
+----------+---------+

+----------+---------+
| Employee | Salary |
+----------+---------+
| Kiran | 75000.0 |
+----------+---------+

Each SELECT inside the procedure appears as a separate result set in MySQL CLI.

Step 5 — Create Students and Student_Audit tables

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Dept VARCHAR(30)
);
CREATE TABLE Student_Audit (
AuditID INT AUTO_INCREMENT PRIMARY KEY,
StudentID INT,
Action VARCHAR(100),
ActionTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Screenshot:

Step 6 — Create AFTER INSERT trigger on Students
DROP TRIGGER IF EXISTS AfterStudentInsert;
DELIMITER $$
CREATE TRIGGER AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, Action)
VALUES (NEW.StudentID, CONCAT('Student ', NEW.StudentName, ' registered'));
END$$
DELIMITER ;

Screenshot:

Step 7 — Insert sample students and view audit log

INSERT INTO Students (StudentID, StudentName, Dept) VALUES (101, 'Rahul', 'CSE');
INSERT INTO Students (StudentID, StudentName, Dept) VALUES (102, 'Anita', 'ECE');
SELECT * FROM Student_Audit;

Expected Output:
+---------+-----------+---------------------------+---------------------+
| AuditID | StudentID | Action | ActionTime |
+---------+-----------+---------------------------+---------------------+
| 1 | 101 | Student Rahul registered | 2025-10-01 11:30:00 |
| 2 | 102 | Student Anita registered | 2025-10-01 11:31:00 |
+---------+-----------+---------------------------+---------------------+

Step 8 — Verification Queries
SELECT * FROM Employee;
SELECT * FROM Students;
SELECT * FROM Student_Audit;
SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;

Screenshot:

Step 10 — Full ready-to-run .sql script
Save the SQL from Steps 1–7 into demo_script.sql and run:
mysql -u root -p < demo_script.sql

Troubleshooting Tips
1.DELIMITER errors — Use MySQL CLI; GUI clients sometimes reject DELIMITER.
2.Cursor/Handler errors — Ensure DECLARE statements are at the start of the block.
3.Trigger not firing — Check table names and case sensitivity.
4.Permission issues — Ensure your MySQL user has CREATE ROUTINE, CREATE TRIGGER, INSERT privileges.