Triggers

Code

DELIMITER //
CREATE TRIGGER orders_audit_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_audit(order_id, action, changed_at)
  VALUES (NEW.id, 'INSERT', NOW());
END //

CREATE TRIGGER products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'price cannot be negative';
  END IF;
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- Inspect and drop
SHOW TRIGGERS LIKE 'orders%';
DROP TRIGGER orders_audit_after_insert;

Explanation

Triggers fire automatically BEFORE or AFTER a row-level INSERT, UPDATE, or DELETE, allowing auditing, validation, or derived column updates. NEW and OLD refer to the new and previous row values respectively. SIGNAL aborts the statement with a custom error, useful for enforcing business rules.

More MySQL Snippets