Triggers, Cursors & Events

Triggers

A trigger runs automatically before or after an INSERT, UPDATE or DELETE on a table. MySQL supports six combinations: BEFORE/AFTER × the three events.

DELIMITER //

-- audit every salary change
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_by, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, USER(), NOW());
  END IF;
END //

-- validate and normalise before insert
CREATE TRIGGER trg_student_before_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
  IF NEW.cgpa < 0 OR NEW.cgpa > 4 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CGPA must be between 0 and 4';
  END IF;
END //

DELIMITER ;

OLD and NEW give access to the row values:

Event	OLD	NEW
INSERT	not available	the incoming row
UPDATE	the row before	the row after
DELETE	the row being removed	not available

Only a BEFORE trigger can modify NEW values.

SHOW TRIGGERS FROM school;
DROP TRIGGER IF EXISTS trg_salary_audit;

Limitations: a trigger cannot CALL a procedure that returns a result set, cannot start or commit transactions, and does not fire for TRUNCATE or for cascaded foreign-key actions in the way you might expect. Keep trigger bodies short — they run inside the caller's transaction, so slow logic slows every write.

Cursors

A cursor walks a result set row by row inside a stored program. Use it only when set-based SQL genuinely cannot express the task.

DELIMITER //
CREATE PROCEDURE award_scholarships()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_cgpa DECIMAL(3,2);

  DECLARE cur CURSOR FOR SELECT id, cgpa FROM students WHERE cgpa >= 3.5;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_cgpa;
    IF done = 1 THEN LEAVE read_loop; END IF;

    INSERT INTO scholarships (student_id, amount)
    VALUES (v_id, IF(v_cgpa >= 3.9, 50000, 25000));
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

The same job as one statement is far faster:

INSERT INTO scholarships (student_id, amount)
SELECT id, IF(cgpa >= 3.9, 50000, 25000) FROM students WHERE cgpa >= 3.5;

Events (the MySQL scheduler)

Events are cron jobs that live inside the database.

SET GLOBAL event_scheduler = ON;

DELIMITER //
CREATE EVENT ev_nightly_cleanup
ON SCHEDULE EVERY 1 DAY STARTS '2026-01-01 02:00:00'
DO
BEGIN
  DELETE FROM sessions WHERE expires_at < NOW();
  INSERT INTO daily_stats (day, total_students)
  SELECT CURDATE(), COUNT(*) FROM students;
END //
DELIMITER ;

-- one-off
CREATE EVENT ev_once ON SCHEDULE AT NOW() + INTERVAL 1 HOUR
DO UPDATE promotions SET active = 0;

SHOW EVENTS;
ALTER EVENT ev_nightly_cleanup DISABLE;
DROP EVENT IF EXISTS ev_nightly_cleanup;

Events are convenient but invisible to your application's logs and deployment pipeline. For anything important, prefer a real scheduler (cron, systemd timers, a job queue) that you can monitor and alert on.