Understanding Cursors & Triggers in DBMS

Introduction

Databases are the backbone of almost every software application today. From banking systems to e-commerce sites, managing and manipulating data efficiently is crucial. While SQL gives us powerful tools for querying and updating data, there are advanced features in DBMS like Cursors and Triggers that allow us to handle complex situations with ease.

In this blog, we will explore:

• What Cursors are, why we need them, and how to use them.
• What Triggers are, how they help automate actions, and practical examples.

Cursors in DBMS

What is a Cursor?

A Cursor in DBMS is a database object that allows us to fetch and process query results row by row. Normally, SQL works with sets of data all at once, but sometimes we need to handle each row individually. That’s where cursors come in.

Think of it like a pointer that moves through the rows of a result set one at a time.

Why Use Cursors?

• When we need row-by-row processing.
• To perform custom logic on each record.
• To store results temporarily and use them later.

Example: Cursor for High Salary Employees
Suppose we have an Employee table:

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES
(1, 'Bob Smith', 60000),
(2, 'Charlie Brown', 55000),
(3, 'Edward Wilson', 75000),
(4, 'Fiona Davis', 52000),
(5, 'Hannah Lee', 60000);
(6,'naveens',67000);

Now, let’s create a stored procedure with a cursor that selects employees earning more than 50,000.

DELIMITER $$

CREATE PROCEDURE get_high_salary_employees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_emp_name VARCHAR(100);

    DECLARE emp_cursor CURSOR FOR
        SELECT emp_name FROM Employee WHERE salary > 50000;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    CREATE TABLE IF NOT EXISTS high_salary_emp(emp_name VARCHAR(100));
    TRUNCATE TABLE high_salary_emp;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO v_emp_name;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO high_salary_emp VALUES (v_emp_name);
    END LOOP;

    CLOSE emp_cursor;

    SELECT * FROM high_salary_emp;
END$$

DELIMITER ;

CALL get_high_salary_employees();

Output:

This shows how a cursor fetches each row one by one and stores it into another table.

Triggers in DBMS

A Trigger is a special type of stored program that automatically executes when a specific event occurs in a table. Events can be:

• INSERT
• UPDATE
• DELETE

In simple words, triggers are rules that fire automatically when data changes.

Why Use Triggers?

• To maintain data integrity.
• To automatically log changes.
• To enforce business rules.

Example 1: AFTER INSERT Trigger (Audit Log)

We want to keep track of all new employees added. So, we create an audit table:

CREATE TABLE Employee_Audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, the trigger:

DELIMITER $$

CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO Employee_Audit (emp_id, emp_name, salary)
    VALUES (NEW.emp_id, NEW.emp_name, NEW.salary);
END$$

DELIMITER ;

Test it:

INSERT INTO Employee VALUES (34, 'Meera', 72000);

SELECT * FROM Employee_Audit;

Output shows Meera automatically logged in Employee_Audit.

Conclusion

• Cursors let us handle query results row by row, useful for special operations.
• Triggers allow us to automatically enforce rules or log changes when data is inserted, updated, or deleted.
• Together, they give DBMS more power, automation, and data integrity.