Triggers

In short: A trigger is a set of statements the database runs on its own on INSERT, UPDATE or DELETE in a table, for example to log price changes or update the stock.

Problem

Who changed the price?

A customer complains that yesterday the coffee cost less. The database has only today's price. When was it changed, and from what? There is no way to tell.

You could make every program that touches prices also write to a log. But someone will forget: a script for a price rise, a manual fix, a new program a year from now.

The idea

A trigger

A trigger is a query the database runs by itself when something changes in a table. It is written once and applies to everyone who writes to the database.

SQLdatabase: shop

CREATE TABLE price_log (
  product_id INTEGER NOT NULL,
  old_price REAL NOT NULL,
  new_price REAL NOT NULL
);

CREATE TRIGGER log_price
AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);
END;

UPDATE products SET price = 2.6 WHERE id = 11;
UPDATE products SET price = 16.5 WHERE id = 17;
SELECT * FROM price_log;

CREATE TABLE price_log ( product_id INTEGER NOT NULL, old_price REAL NOT NULL, new_price REAL NOT NULL ); CREATE TRIGGER log_price AFTER UPDATE OF price ON products WHEN OLD.price <> NEW.price BEGIN INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price); END; UPDATE products SET price = 2.6 WHERE id = 11; UPDATE products SET price = 16.5 WHERE id = 17; SELECT * FROM price_log;

▶ RunRestore the queryFresh database

• AFTER UPDATE OF price ON products: after every change of the price.
• WHEN OLD.price <> NEW.price: only if the price really is different.
• OLD is the row before the change, NEW after it.
• Between BEGIN and END are the trigger's queries. Here they do not mean a transaction, but its body.

Both price changes are in the log, without the program knowing about it.

See it

The stock by itself

A trigger can also maintain data: every new line in an order reduces the product's stock.

SQLdatabase: shop

CREATE TRIGGER take_stock
AFTER INSERT ON order_items
BEGIN
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;

INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new');
INSERT INTO order_items VALUES (61, 10, 3, 2.1);
SELECT id, name, stock FROM products WHERE id = 10;

CREATE TRIGGER take_stock AFTER INSERT ON order_items BEGIN UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id; END; INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new'); INSERT INTO order_items VALUES (61, 10, 3, 2.1); SELECT id, name, stock FROM products WHERE id = 10;

▶ RunRestore the queryFresh database

The apples were 80, now they are 77. The trigger runs in the same transaction as the INSERT. If the order is rolled back, so is the stock.

Try it

An alert for a bad review

The alerts table (review_id, product_id) already exists. Write a trigger that, on a new review with rating 1, writes the review number and the product into alerts. The check adds two reviews, rated 1 and 5, and looks at alerts.

SQLdatabase: shop

-- The alerts table already exists.
SELECT * FROM alerts;

-- The alerts table already exists. SELECT * FROM alerts;

Check▶ Run

The idea

When yes, when no

Triggers are good for things that must happen always, whatever program writes: a log of changes, keeping a computed column up to date, a check that CHECK cannot do.

Their price: the logic is hidden. Whoever reads the program's code sees one INSERT, and the database does three more things. A trigger that fires a trigger that fires a trigger is hard to debug. Business rules such as discounts and offers are usually clearer in the program's code.

More precisely, if you are curious

In PostgreSQL the body of a trigger is a separate function (CREATE FUNCTION … RETURNS trigger, then CREATE TRIGGER … EXECUTE FUNCTION). There are also BEFORE triggers, which can change NEW before the row goes in. SQLite has both BEFORE and INSTEAD OF for views.

Test

Check yourself

1. What are OLD and NEW in a trigger after UPDATE?

The row before and after the changeThe old and the new tableThe first and last row of the table

2. A trigger runs…

In the same transaction as the query that woke it upOnly if the program calls itIn a separate transaction, later

3. Which is a downside of triggers?

The logic is hidden: whoever reads the program's code does not see that the database does something moreThey are always slowThey do not work with INSERT

Check

Start the lesson over

Related lessons

• Builds on: INSERT: new rows · UPDATE: changing rows · CREATE TABLE: a new table · ACID: all or nothing
• Continues in: Searching text · The bakery, stage 5: the database works on its own · The bakery, stage 6: the report, the copy and DROP