Cursor and Trigger

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.