Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A trigger fails with error 1363 because it reads a row that does not exist for that event

ErrorERROR 1363 (HY000): There is no OLD row in on INSERT trigger

Short answer

A MySQL trigger sees different rows per event. An INSERT trigger has only NEW, a DELETE trigger has only OLD, and only an UPDATE trigger has both. Error 1363 means the body reads the row that its event does not have. Use NEW in the INSERT trigger, OLD in the DELETE trigger, and write one trigger per event.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Write down the event word in the CREATE TRIGGER line, then find every OLD. and NEW. in the body. If the trigger already exists on the server, print its text.

    SHOW CREATE TRIGGER trg_orders_audit\G
  2. 2Match what you see, then jump to its fix

    1. 1There is no OLD row in on INSERT triggerAn INSERT trigger reads OLDFix
    2. 2The same error 1363, but the message names NEW and the DELETE eventA DELETE trigger reads NEWFix
    3. 3The body mixes OLD and NEW, and the trigger header was meant to cover INSERT and UPDATE or DELETE togetherOne trigger body is used for several eventsFix

What does the error look like?

  • ERROR 1363 (HY000): There is no OLD row in on INSERT trigger
  • There is no %s row in %s trigger
  • ER_TRG_NO_SUCH_ROW_IN_TRG

Cause 1 of 3

An INSERT trigger reads OLD

1Check

A row that is being inserted has no earlier version, so OLD.some_column has nothing to point at. This often happens when an audit trigger is copied from an UPDATE trigger, where OLD makes sense, and only the event word is changed.

-- fails: an INSERT has no old row
CREATE TRIGGER trg_orders_audit AFTER INSERT ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, status) VALUES (OLD.id, OLD.status);

2Fix

In an INSERT trigger, use NEW for every column. If you also wanted to record a previous value on insert, there is none: log a fixed marker such as NULL instead.

DROP TRIGGER IF EXISTS trg_orders_audit;
CREATE TRIGGER trg_orders_audit AFTER INSERT ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, status) VALUES (NEW.id, NEW.status);

3Verify

The CREATE TRIGGER statement returns without error, and the definition printed by the server shows only NEW.

SHOW CREATE TRIGGER trg_orders_audit\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

A DELETE trigger reads NEW

1Check

The error is the same one turned around: the message template is 'There is no %s row in %s trigger'. A row being deleted has no new version, so NEW cannot be read. The mistake often comes from copying an INSERT trigger and changing only its event.

-- fails: a DELETE has no new row
CREATE TRIGGER trg_orders_deleted AFTER DELETE ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, status) VALUES (NEW.id, 'deleted');

2Fix

In a DELETE trigger, use OLD for every column. OLD is the row as it was before it was removed.

DROP TRIGGER IF EXISTS trg_orders_deleted;
CREATE TRIGGER trg_orders_deleted AFTER DELETE ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, status) VALUES (OLD.id, 'deleted');

3Verify

Create the trigger, delete a test row, and read the log table.

DELETE FROM orders WHERE id = 9999;
SELECT * FROM orders_log ORDER BY 1 DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

One trigger body is used for several events

1Check

A MySQL trigger is bound to exactly one event: the syntax takes INSERT, UPDATE or DELETE, not a list. A body that reads both OLD and NEW can only work in an UPDATE trigger, so it fails as soon as it is attached to any other event.

-- one event per trigger in the header
-- CREATE TRIGGER name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table FOR EACH ROW ...
SHOW CREATE TRIGGER trg_orders_audit\G

2Fix

Create one trigger for each event, and in each body use only the row alias that event has. Several triggers on the same table are allowed, and they run in the order they were created unless you set FOLLOWS or PRECEDES.

CREATE TRIGGER trg_orders_ai AFTER INSERT ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, old_status, new_status)
  VALUES (NEW.id, NULL, NEW.status);

CREATE TRIGGER trg_orders_au AFTER UPDATE ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, old_status, new_status)
  VALUES (NEW.id, OLD.status, NEW.status);

CREATE TRIGGER trg_orders_ad AFTER DELETE ON orders
FOR EACH ROW
  INSERT INTO orders_log (order_id, old_status, new_status)
  VALUES (OLD.id, OLD.status, NULL);

3Verify

Insert, update and delete one test row, then read the log table: three rows, one per event.

INSERT INTO orders (id, status) VALUES (9999, 'new');
UPDATE orders SET status = 'paid' WHERE id = 9999;
DELETE FROM orders WHERE id = 9999;
SELECT * FROM orders_log WHERE order_id = 9999;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep one trigger per event and name each by its event (for example _ai, _au, _ad), so the header always tells which alias the body may use.
  • Never copy a trigger body to another event without reading each OLD. and NEW. in it.
  • Keep trigger definitions in migration files, so a wrong alias fails in review or in CI and not on a live server.
  • In an UPDATE trigger, compare OLD and NEW on purpose, and remember that a SET NEW assignment only has an effect in a BEFORE trigger.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

A trigger or stored function fails with error 1442 on its own table

ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

MySQL forbids a stored function or trigger from modifying a table that the invoking statement is already reading or writing.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.