Triggers and Events

Triggers fire on row changes#

A trigger runs automatically BEFORE or AFTER an INSERT, UPDATE, or DELETE on a table. Inside, NEW is the incoming row and OLD is the existing one.

CREATE TABLE price_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_price DECIMAL(10,2),
  new_price DECIMAL(10,2),
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER trg_products_price_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_history (product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
  END IF;
END$$
DELIMITER ;

UPDATE products ──▶ BEFORE trigger ──▶ row written ──▶ AFTER trigger
                      (can modify NEW)                    (audit, side effects)

BEFORE triggers can change NEW values or reject the row with SIGNAL. AFTER triggers are for side effects like audit rows.

Normalizing input in BEFORE#

CREATE TRIGGER trg_customers_email_norm
BEFORE INSERT ON customers
FOR EACH ROW
SET NEW.email = LOWER(TRIM(NEW.email));

The cost#

Triggers run inside the statement that caused them, in the same transaction. A trigger that inserts into another table adds that work to every write. They're also invisible: someone reading application code has no idea an UPDATE writes elsewhere. MySQL has no trigger on SELECT, and triggers aren't fired by cascading foreign key actions.

I use triggers sparingly, mostly for audit trails and small invariants. Anything with branching business logic belongs somewhere you can test it.

SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trg_products_price_audit;

Events: a scheduler inside MySQL#

SET GLOBAL event_scheduler = ON;

CREATE EVENT ev_purge_old_history
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 02:00:00'
DO
  DELETE FROM price_history
  WHERE changed_at < NOW() - INTERVAL 90 DAY
  LIMIT 10000;

Events run in a background thread, as a specified definer. Good for housekeeping like purging old rows, rolling up daily stats, or expiring sessions.

SHOW EVENTS;
ALTER EVENT ev_purge_old_history DISABLE;

Check event_scheduler after server restarts. It resets unless set in config. Also note that in a replicated setup, events can run on a replica too unless you control them, which leads to duplicated work.

Common mistakes#

• Using triggers for business logic that nobody can find later.
• Assuming cascading deletes fire triggers.
• Deleting millions of rows in one event run instead of batches.
• Forgetting the scheduler is off after a restart.

Practice#

Create the audit trigger above and change a few prices. Verify that price_history records only real changes. Then write a BEFORE INSERT trigger on order_items that rejects qty of zero or less using SIGNAL. Last, schedule an event that runs every minute and logs a row to a heartbeat table, then disable it.