This content originally appeared on DEV Community and was authored by Divya _Sundarasekaran
Cursor: Display Employees with Salary > 50,000
A cursor allows you to go row by row through a query result. Here, we will display employee names whose salary is greater than 50,000.
CREATE DATABASE school;
USE school;
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);
INSERT INTO Employee VALUES (1, 'Alice', 60000);
INSERT INTO Employee VALUES (2, 'Bob', 45000);
INSERT INTO Employee VALUES (3, 'Charlie', 70000);
DELIMITER $$
CREATE PROCEDURE EmployeeCursorProc()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE emp_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT EmpName FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_name;
IF done THEN
LEAVE read_loop;
END IF;
SELECT emp_name;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
CALL EmployeeCursorProc();
Trigger: AFTER INSERT on Students Table
A trigger automatically executes an action when a specific event happens in the database. Here, we will log student registrations.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Course VARCHAR(50)
);
CREATE TABLE Student_Audit (
AuditID INT AUTO_INCREMENT PRIMARY KEY,
StudentID INT,
Action VARCHAR(50),
ActionDate DATETIME
);
DELIMITER $$
CREATE TRIGGER trg_Student_Insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, Action, ActionDate)
VALUES (NEW.StudentID, 'Inserted', NOW());
END$$
DELIMITER ;
INSERT INTO Students (StudentID, StudentName, Course)
VALUES (1, 'John Doe', 'Mathematics');
SELECT * FROM Student_Audit;
Summary:
Cursors let you process each row of a query result individually. In this example, it helps display only employees with salary above 50,000.
Triggers automate database tasks. Here, the AFTER INSERT trigger automatically logs student registrations into Student_Audit without manual effort.
Using cursors and triggers together helps maintain data integrity and reduces repetitive work in a database.
This content originally appeared on DEV Community and was authored by Divya _Sundarasekaran
Divya _Sundarasekaran | Sciencx (2025-10-02T05:34:34+00:00) Cursor and Trigger. Retrieved from https://www.scien.cx/2025/10/02/cursor-and-trigger/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.