🏦 Database Magic: Automating Tasks with Cursor and Trigger ✨

Managing a database isn’t just about keeping information — it’s about automating how data behaves and reacts.

In this article, we’ll explore two powerful SQL components that help add automation and intelligence to your database:

🎯 Cursor – processes query results one row at a time
⚙️ Trigger – executes specific actions automatically after data changes

Let’s understand how both work! 🔍

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

A cursor is a database mechanism that allows you to handle query results row by row — ideal when you need to process records individually or apply specific logic.

🧩 **Step 1: Create the Employee Table & Insert Sample Records

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 inserts some sample data.
A few employees have salaries greater than ₹50,000, which we’ll filter using a cursor.

⚙️ Step 2: Declare and Execute the Cursor

We’ll now define a stored procedure that uses a cursor to display the names of employees earning more than ₹50,000.

CODE:

DELIMITER $$

CREATE PROCEDURE DisplayHighSalaryEmployees()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE empName VARCHAR(50);
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeName FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

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:

The cursor selects employees whose salary exceeds ₹50,000.

The handler ensures the loop exits when there are no more rows left to fetch.

Commands like OPEN, FETCH, and CLOSE control the cursor’s operation.

For each fetched row, the employee name is displayed using a SELECT statement.

When executed, this stored procedure loops through all qualifying employees and prints their names one by one.

⚡ Part 2: AFTER INSERT Trigger – Auto Logging New Student Registrations

A trigger in SQL is a block of code that executes automatically when a specific event occurs in a table.
Here, we’ll create a trigger that logs every new student registration in a separate audit table.

🧩 Step 1: Create the 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 );

✅ The Students table stores student information, and the Student_Audit table will maintain a log of each registration.

⚙️** Step 2: Create the AFTER INSERT Trigger**

This trigger fires automatically right after a new record is inserted into the Students table.

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 recently inserted record.
✅ NOW() stores the exact timestamp when the student was added.

🧪** 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;

🧾** Output:**

✅ Conclusion

Both Cursors and Triggers help bring automation and logic directly into your SQL environment.

💡 Use Cursors when you need to process rows one at a time with specific conditions.
⚙️ Use Triggers when you want automatic actions to occur after table modifications.

🚀 Apply these concepts to your projects and make your databases more dynamic and responsive!