Triggers and Events

Automatic, database-side logic that fires on data changes or on a schedule

Big idea: A trigger is a block of SQL the database runs automatically every time a row is inserted, updated or deleted in a table — you never call it, the engine does. An event is the same idea on a clock: SQL that runs on a schedule with no application involved. Both push logic into the database, which is powerful for audit trails and derived data, but it is also hidden logic that no grep of your application code will reveal.

By the end of this topic you will be able to:

write BEFORE and AFTER triggers for INSERT, UPDATE and DELETE;
read and write the NEW and OLD pseudo-rows correctly;
build the two classic patterns: an audit log and derived / denormalised data;
validate or reject a change with SIGNAL;
schedule recurring maintenance with CREATE EVENT; and
judge honestly when hidden database logic helps and when it hurts maintainability.

1. The example schema

Every query in this topic runs against one small shop database. We keep products, the orders customers place, the line items on each order, and an audit table that will record price changes.

CREATE TABLE products (
  product_id  INT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(80)   NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  stock       INT           NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY AUTO_INCREMENT,
  customer    VARCHAR(80) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'new',
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
  item_id     INT PRIMARY KEY AUTO_INCREMENT,
  order_id    INT NOT NULL,
  product_id  INT NOT NULL,
  quantity    INT NOT NULL
);

CREATE TABLE price_audit (
  audit_id    INT PRIMARY KEY AUTO_INCREMENT,
  product_id  INT NOT NULL,
  old_price   DECIMAL(10,2),
  new_price   DECIMAL(10,2),
  changed_by  VARCHAR(80),
  changed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price, stock) VALUES
  ('Notebook', 3.50, 100),
  ('Pen',      1.20,  40),
  ('Backpack', 25.00, 10);

Three products are on the shelf. Everything below adds behaviour that runs automatically when these tables change.

Triggers and Events

Triggers and Events

1. The example schema

Continue this lesson on YourAIZone

In the full lesson