Triggers & Cursors

Triggers

A Trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. Most commonly used for auditing, enforcing complex integrity rules, or synchronizing tables.

Trigger Types

Trigger Example (Audit Log)

CREATE TRIGGER LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO SalaryAudit(emp_id, old_sal, new_sal, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;

Trigger to Update Timestamp

CREATE TRIGGER UpdateModifiedAt
BEFORE UPDATE ON Users
FOR EACH ROW
BEGIN
    SET NEW.modified_at = NOW();
END;

Trigger to Prevent Deletion

CREATE TRIGGER PreventAdminDelete
BEFORE DELETE ON Users
FOR EACH ROW
BEGIN
    IF OLD.role = 'admin' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete admin users';
    END IF;
END;

Cursors

A Cursor is a database object used to retrieve data from a result set one row at a time. SQL is fundamentally set-based, meaning it’s designed to work on all rows at once. Cursors are row-based and are generally much slower.

Cursor Example

DECLARE emp_cursor CURSOR FOR
    SELECT id, salary FROM employees WHERE department_id = 5;

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process each row
    UPDATE employees SET salary = @salary * 1.1 WHERE id = @emp_id;

    FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Why Cursors Are Slow

1. Row-by-row processing: Each row requires a separate fetch, which means many round-trips within the database engine.
2. Lock overhead: Cursors often hold locks longer than set-based operations.
3. No optimisation: The database cannot parallelise or optimise row-by-row operations.
4. Memory: Cursors may materialise the entire result set in memory.

Replace Cursors with Set-Based Operations

-- BAD: Cursor loop applying 10% raise
-- DECLARE cursor... FETCH... UPDATE... LOOP

-- GOOD: Single set-based UPDATE
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5;

Most common cursor use cases can be replaced with:

• UPDATE with CASE expressions
• Window functions (LAG, LEAD, ROW_NUMBER)
• Recursive CTEs for hierarchical processing

CREATE TRIGGER LogSalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO SalaryAudit(emp_id, old_sal, new_sal, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;

CREATE TRIGGER PreventAdminDelete
BEFORE DELETE ON Users
FOR EACH ROW
BEGIN
    IF OLD.role = 'admin' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete admin';
    END IF;
END;

-- BAD (Cursor)
-- Loop { UPDATE employees SET salary = salary * 1.1 WHERE id = @current_id }

-- GOOD (Set-based)
UPDATE employees SET salary = salary * 1.1 WHERE dept = 'Sales';

CREATE TRIGGER ArchiveDeletes
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users_log(id, email, deleted_at)
    VALUES (OLD.id, OLD.email, NOW());
END;

CREATE TRIGGER UpdateTimestamp
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END;