Triggers

What this page covers

Introduction

What is it? A trigger is a piece of SQL that the database runs automatically when a row is inserted, updated, or deleted in a specific table. You do not call it. The write itself calls it.

Why do we need it? Some rules must hold no matter who writes to the table: the main app, a Python script someone ran at 2 AM, or a DBA fixing data by hand. If the rule lives only in the app, the script and the DBA skip it. A trigger lives inside the table, so every write goes through it.

Real-world analogy A trigger is like the CCTV camera at a shop door. Nobody switches it on for each customer. Anyone who walks in (inserts), changes something (updates), or walks out with something (deletes) gets recorded. The downside is the same as CCTV: it runs for everyone, all the time, and people forget it is there.

Business use case A payroll team in Hyderabad gets an audit request: "Show every salary change in the last year, who made it, the old value, and the new value." Some changes came from the HR app, some from bulk scripts, a few from manual SQL. Only a trigger on the employees table would have caught all three.

Dataset Used

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY AUTO_INCREMENT,
  full_name  VARCHAR(100) NOT NULL,
  city       VARCHAR(50) NOT NULL,
  salary     DECIMAL(10,2) NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE salary_audit (
  audit_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
  emp_id     INT NOT NULL,
  old_salary DECIMAL(10,2) NOT NULL,
  new_salary DECIMAL(10,2) NOT NULL,
  changed_by VARCHAR(100) NOT NULL,
  changed_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL,
  order_count INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO employees (full_name, city, salary) VALUES
  ('Aarav Mehta', 'Bengaluru', 2400000.00),
  ('Priya Nair',  'Mumbai',    1500000.00);

INSERT INTO customers VALUES (1, 'Kavya Reddy', 0), (2, 'Arjun Singh', 0);

employees

customers

Beginner Core Path: What Runs, and When?

Read this lifecycle before writing trigger syntax:

1. An ordinary INSERT, UPDATE, or DELETE targets one or more rows.
2. A BEFORE trigger runs before each target row is stored. It can clean NEW values or reject the row.
3. The database writes that row and checks its constraints.
4. An AFTER trigger runs after that row was written. It is the usual place for an audit row or counter change.
5. The next affected row repeats the same process.
6. COMMIT makes the original write and all trigger side effects permanent together. ROLLBACK removes them together.

OLD, NEW, and row count first

MySQL triggers are always FOR EACH ROW. If one UPDATE affects 10,000 rows, its row trigger runs 10,000 times; it is not one trigger call for the whole statement. PostgreSQL also supports statement-level triggers, covered in Deep Dive.

Only a BEFORE trigger can assign to NEW.col. An AFTER trigger can read OLD and NEW, but the target row is already stored.

Run a tiny BEFORE trigger

The shortest useful trigger needs no BEGIN ... END block:

CREATE TRIGGER trg_employees_trim_name
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.full_name = TRIM(NEW.full_name);

INSERT INTO employees (full_name, city, salary)
VALUES ('  Neha Rao  ', 'Pune', 1200000.00);

SELECT full_name FROM employees WHERE full_name = 'Neha Rao';
-- Returns: Neha Rao (without the surrounding spaces)

The application issued a normal INSERT. The trigger changed the incoming NEW.full_name before the row was stored.

MySQL CLI only: DELIMITER is not SQL and is not part of a trigger. Use it only in clients such as the mysql command-line program that split input at semicolons. JDBC, application drivers, and migration tools normally send the complete CREATE TRIGGER statement directly and should not be given DELIMITER unless that tool explicitly supports client commands.

Prefer a declarative feature when it can express the rule: CHECK for a row condition, a default for a missing value, a generated column for a calculation, or MySQL's ON UPDATE CURRENT_TIMESTAMP for updated_at. Reach for a trigger when the rule needs OLD versus NEW, must write an audit/outbox row for every writer, or cannot be expressed declaratively.

Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
[{FOLLOWS | PRECEDES} other_trigger_name]
trigger_body;

Mental Model

The trigger runs inside the same transaction as the statement that fired it. If the trigger fails, the statement fails, and on InnoDB everything that statement did is rolled back, including the trigger's own writes.

1. The Six Kinds of Trigger

MySQL supports FOR EACH ROW triggers on BEFORE or AFTER each of INSERT, UPDATE, DELETE. That gives six combinations.

INSERT ... ON DUPLICATE KEY UPDATE can fire both insert and update triggers. REPLACE can fire delete and insert triggers. Keep that in mind when you count trigger runs.

2. NEW and OLD

• OLD is always read-only.
• NEW can be changed with SET NEW.col = ..., but only in a BEFORE trigger. By the time AFTER runs, the row is already written.
• In a BEFORE INSERT trigger, an AUTO_INCREMENT column in NEW is still 0. The id is assigned later. If you need the new id (for an audit row, say), use an AFTER INSERT trigger.

3. DELIMITER in MySQL

DELIMITER belongs to the MySQL command-line client, not to the MySQL SQL language or server. A trigger body with more than one statement needs BEGIN ... END, and each statement inside ends with ;. In the mysql command-line client, the first ; would otherwise send a half-written trigger to the server. DELIMITER tells that client to use a different end marker temporarily. It is not portable SQL and it is not needed merely because the server is MySQL.

DELIMITER $$

CREATE TRIGGER trg_example
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.full_name = TRIM(NEW.full_name);
  SET NEW.city = TRIM(NEW.city);
END$$

DELIMITER ;

DELIMITER is a client command, not SQL. The server never sees it. App drivers (JDBC, mysql2 in Node) do not need it, because you send the whole CREATE TRIGGER as one statement.

4. Example: Audit Log for Salary Changes

DELIMITER $$

CREATE TRIGGER trg_employees_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary <> OLD.salary THEN
    INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_by, changed_at)
    VALUES (OLD.emp_id, OLD.salary, NEW.salary,
            COALESCE(@app_user, USER()), NOW());
  END IF;
END$$

DELIMITER ;

SET @app_user = 'hr.manager@company.in';
UPDATE employees SET salary = 2640000.00 WHERE emp_id = 1;
UPDATE employees SET city = 'Pune' WHERE emp_id = 2;   -- no audit row, salary unchanged

SELECT emp_id, old_salary, new_salary, changed_by FROM salary_audit;

Two details that interviewers like:

• Who made the change? Your app connects with one database user (say hr_app), so USER() tells you nothing about the real person. Set a session variable like @app_user from the app before the write. Inside a trigger, CURRENT_USER() returns the trigger's definer, not the caller, so use USER() as the fallback.
• Why AFTER? The audit row should be written only if the update itself went through.

One production trap with @app_user: session variables live on the connection, and connection pools reuse connections. If one request sets @app_user and the next request on the same connection forgets to, the audit log blames the wrong person. Set it at the start of every request, or reset it when the connection goes back to the pool.

5. Example: Keeping updated_at

CREATE TRIGGER trg_employees_touch
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.updated_at = NOW();

This works, but in MySQL you do not need a trigger for it. A column attribute does the same job with no hidden code:

ALTER TABLE employees
MODIFY updated_at DATETIME NOT NULL
  DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP;

PostgreSQL has no ON UPDATE column attribute, so there a small BEFORE UPDATE trigger is the normal way to keep updated_at. (EXECUTE FUNCTION needs PostgreSQL 11 or later. Older versions write EXECUTE PROCEDURE.)

-- PostgreSQL
CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_employees_touch
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Notice the PostgreSQL shape: the logic lives in a function, and the trigger just points at it.

6. Example: Validating Data with SIGNAL

Rule from finance: no single salary update may cut pay by more than 30 percent. A typo like 240000 instead of 2400000 should be stopped.

DELIMITER $$

CREATE TRIGGER trg_employees_salary_guard
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary * 0.7 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Salary cut above 30 percent is not allowed';
  END IF;
END$$

DELIMITER ;

UPDATE employees SET salary = 240000.00 WHERE emp_id = 1;
-- ERROR 1644 (45000): Salary cut above 30 percent is not allowed

SQLSTATE '45000' means "unhandled user-defined exception". The statement fails and nothing is written. The app gets a normal SQL error it can show to the user.

If the rule only looks at the new row (for example salary > 0), use a CHECK constraint instead. MySQL enforces CHECK from 8.0.16. Triggers are only needed when the rule compares old and new values or reads other tables.

7. Example: A Denormalized Counter

The customer profile page shows "Kavya has placed 37 orders". Counting orders every time is slow for heavy users, so the team stores order_count on customers and keeps it in sync with triggers.

CREATE TRIGGER trg_orders_count_insert
AFTER INSERT ON orders
FOR EACH ROW
UPDATE customers
SET order_count = order_count + 1
WHERE customer_id = NEW.customer_id;

CREATE TRIGGER trg_orders_count_delete
AFTER DELETE ON orders
FOR EACH ROW
UPDATE customers
SET order_count = order_count - 1
WHERE customer_id = OLD.customer_id;

INSERT INTO orders (customer_id, amount) VALUES (1, 499.00), (1, 1299.00), (2, 349.00);
SELECT customer_id, order_count FROM customers;

What can go wrong:

• If an order's customer_id is updated, the counts drift unless you also write an AFTER UPDATE trigger.
• Every new order now also locks the customer row. A B2B customer placing 500 orders a minute turns that row into a hot spot.
• TRUNCATE orders does not fire DELETE triggers, so the counts stay wrong.

Keep a nightly job that recomputes the counts with GROUP BY and fixes any drift.

8. Several Triggers on One Event

Since MySQL 5.7 you can have more than one trigger for the same table, timing, and event. They run in creation order unless you say otherwise.

CREATE TRIGGER trg_employees_salary_notify
AFTER UPDATE ON employees
FOR EACH ROW
FOLLOWS trg_employees_salary_audit
INSERT INTO notify_queue (emp_id, created_at) VALUES (NEW.emp_id, NOW());

(notify_queue is a small table a worker reads to send "your salary was revised" emails.)

FOLLOWS x runs after trigger x. PRECEDES x runs before it. In PostgreSQL there is no such clause: triggers on the same event fire in alphabetical order of their names, so teams prefix names like t10_, t20_.

If you need to control the order of three triggers on one event, it is usually a sign that the logic should be one trigger, or should not be in triggers at all.

9. Viewing and Dropping Triggers

SHOW TRIGGERS FROM hr LIKE 'employees';

SHOW CREATE TRIGGER trg_employees_salary_guard;

SELECT trigger_name, action_timing, event_manipulation, action_order
FROM information_schema.TRIGGERS
WHERE event_object_schema = 'hr'
  AND event_object_table  = 'employees'
ORDER BY action_timing, event_manipulation, action_order;

DROP TRIGGER IF EXISTS trg_employees_touch;

There is no ALTER TRIGGER and no CREATE OR REPLACE TRIGGER in MySQL. To change a trigger, drop it and create it again. Both statements commit on their own, so you cannot wrap them in one transaction. Any write that lands between the drop and the create runs with no trigger at all. For an audit trigger that means a missing audit row, so do it in a quiet window.

PostgreSQL is easier here. DDL is transactional, so a drop and create inside one BEGIN ... COMMIT is safe, and PostgreSQL 14 and later also support CREATE OR REPLACE TRIGGER.

Dropping a table drops its triggers too.

10. Why Many Teams Avoid Triggers

Triggers are not bad. They are easy to overuse, and the cost shows up months later, usually in someone else's on-call shift. Here is what tends to go wrong:

What to use instead

-- Generated column instead of a trigger
ALTER TABLE order_items
ADD COLUMN line_total DECIMAL(12,2)
  GENERATED ALWAYS AS (qty * unit_price) STORED;

My rule: use triggers for small, boring rules about the data that must hold for every writer, such as auditing a sensitive column or blocking a dangerous update. Keep business workflows (send an email, call a payment API, update a search index) in application code, where people can see them.

Deep Dive

Statement-level triggers. MySQL only has row-level triggers. PostgreSQL also has FOR EACH STATEMENT triggers, which run once per statement no matter how many rows changed. In an AFTER statement trigger, REFERENCING NEW TABLE AS new_rows lets the trigger see all changed rows at once as a table (PostgreSQL 10 and later). One INSERT ... SELECT into an audit table is much faster than 10,000 single-row inserts.

INSTEAD OF triggers. PostgreSQL (and SQL Server) let you put INSTEAD OF triggers on views to make complex views writable. MySQL does not support them.

Binary logging and replicas. With row-based replication (the MySQL 8 default), replicas receive the final row changes, including those made by triggers, and do not re-run the triggers. With statement-based logging, the replica re-runs the original statement and its triggers fire again there. That can give different results if the trigger uses non-deterministic functions like UUID() or RAND(). (NOW() is safe, because the original timestamp is sent along with the statement.)

Permissions. Creating a trigger needs the TRIGGER privilege on the table. The trigger body runs with the definer's privileges, like a SQL SECURITY DEFINER view.

Real-world Use Cases

• Compliance audit: every change to salary, bank account, or PAN columns recorded with old and new values.
• Safety guards: block deleting a customer who has unpaid invoices, or block a price update that drops a product to ₹1 by mistake.
• PostgreSQL updated_at: one shared touch_updated_at() function attached to every table.
• Legacy systems: a trigger mirrors writes from an old table into a new one during a migration, until the app is switched.
• Archive on delete: an AFTER DELETE trigger copies the row into customers_archive.

Review Tips

• Draw the BEFORE, write, AFTER flow. Say clearly that it all runs in the same transaction.
• Know which of NEW and OLD exist for each event, and that only NEW in a BEFORE trigger can be changed.
• Have one solid example ready (salary audit is the classic).
• Follow "here is how to write it" with "here is why I would be careful", and give an alternative. Interviewers want to hear that you know the cost, not just the syntax.
• In a real system, before you add a trigger, ask who will find it. Put the trigger DDL in the same migration tool as the schema, and mention it in the code that writes to that table.

Common Mistakes to Avoid

Mistake 1: Trying to update the same table inside its own trigger

MySQL raises error 1442. To change the row being written, use SET NEW.col = ... in a BEFORE trigger.

Mistake 2: Setting NEW in an AFTER trigger

The row is already written. MySQL rejects it. Use BEFORE.

Mistake 3: Forgetting DELIMITER in the MySQL CLI

The client cuts the trigger at the first ; and you get a confusing syntax error.

Mistake 4: Auditing with CURRENT_USER() or a shared app user

You record the trigger definer or hr_app, not the real person. Pass the user from the app in a session variable.

Mistake 5: Assuming triggers catch everything

TRUNCATE and foreign key cascades in MySQL skip them. Counters drift.

Mistake 6: Putting slow work or external calls in a trigger

A MySQL trigger cannot call an HTTP API, and heavy queries inside it slow down every write. Write to an outbox table and let a worker do the rest.

Mistake 7: Forgetting the trigger during a bulk backfill

A 2 crore row update runs 2 crore trigger calls, inside one huge transaction if you run it as one statement. MySQL has no switch to turn triggers off for one session, so run the backfill in batches of a few thousand rows and expect it to take longer than the same update without triggers. In PostgreSQL, a superuser can set session_replication_role = replica for a bulk load to skip normal triggers, but then nothing the triggers would have done happens, so you must do that work yourself.

Interview Questions

"What is a trigger?"

A trigger is SQL that the database runs automatically before or after an INSERT, UPDATE, or DELETE on a table. It runs for every writer, in the same transaction as the statement that fired it.

"What are NEW and OLD in a trigger?"

NEW is the row after the change and exists for INSERT and UPDATE. OLD is the row before the change and exists for UPDATE and DELETE. Only NEW in a BEFORE trigger can be modified.

"How do you reject a bad row from a MySQL trigger?"

In a BEFORE trigger, check the condition and run SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'. The statement fails with that message and nothing is written.

"BEFORE or AFTER for an audit log?"

AFTER, so the audit row is written only when the actual change succeeded. Both run in the same transaction, so a rollback removes the audit row too.

"Why do many teams avoid triggers?"

The logic is hidden from people reading the app code, it is hard to debug and test, it slows bulk writes because it runs per row, and in MySQL it silently skips TRUNCATE and foreign key cascades.

"What would you use instead of a trigger to keep updated_at in MySQL?"

The column attribute ON UPDATE CURRENT_TIMESTAMP. It does the same job with no extra code to maintain.

Key Takeaways

• A trigger runs automatically on INSERT, UPDATE, or DELETE, BEFORE or AFTER the row is written.
• MySQL has only row-level triggers. PostgreSQL also has statement-level and INSTEAD OF triggers.
• NEW is the new row, OLD is the old row. Only NEW in a BEFORE trigger can be changed.
• Use DELIMITER in the MySQL CLI for multi-statement trigger bodies.
• SIGNAL SQLSTATE '45000' rejects a row with a clear error.
• Triggers run inside the same transaction as the statement. A failure rolls back both.
• Order multiple triggers on one event with FOLLOWS and PRECEDES in MySQL.
• Prefer CHECK constraints, generated columns, ON UPDATE CURRENT_TIMESTAMP, app code, or outbox/CDC when they fit. Keep triggers for small data rules that every writer must obey.