📋

Description

This error indicates that a MariaDB trigger is attempting to reference a row type (such as OLD or NEW) that does not exist or is not valid within its current operational context. For example, an INSERT trigger does not have an OLD row, and a DELETE trigger does not have a NEW row.

💬

Error Message

There is no %s row in %s trigger

🔍

Known Causes

3 known causes

⚠️

Accessing Invalid Row Type

A trigger attempts to access the 'OLD' row in an INSERT trigger or the 'NEW' row in a DELETE trigger, where these row concepts are not applicable.

⚠️

Incorrect Trigger Context

The trigger code's logic incorrectly assumes the presence of an 'OLD' or 'NEW' row alias in a context where it is not available.

⚠️

Syntax Error or Misunderstanding

A typo or a fundamental misunderstanding of when 'OLD' and 'NEW' keywords are valid within different trigger types (BEFORE/AFTER INSERT/UPDATE/DELETE).

🛠️

Solutions

3 solutions available

1. Correct Trigger Syntax for Row References easy

Ensure `OLD` and `NEW` are used correctly within trigger definitions.

1

Review the trigger definition that is causing the error. The error message 'There is no %s row in %s trigger' often indicates that you are trying to reference `OLD` or `NEW` in a context where it's not applicable. For example, `OLD` cannot be used in a `BEFORE INSERT` trigger, and `NEW` cannot be used in a `BEFORE DELETE` trigger.

2

For `INSERT` triggers (both `BEFORE` and `AFTER`), you can reference `NEW` to access the values being inserted. `OLD` is not available.

CREATE TRIGGER my_insert_trigger BEFORE INSERT ON my_table FOR EACH ROW BEGIN
  -- You can reference NEW.column_name here
END;

3

For `DELETE` triggers (both `BEFORE` and `AFTER`), you can reference `OLD` to access the values being deleted. `NEW` is not available.

CREATE TRIGGER my_delete_trigger BEFORE DELETE ON my_table FOR EACH ROW BEGIN
  -- You can reference OLD.column_name here
END;

4

For `UPDATE` triggers (both `BEFORE` and `AFTER`), you can reference both `OLD` (the values before the update) and `NEW` (the values after the update).

CREATE TRIGGER my_update_trigger BEFORE UPDATE ON my_table FOR EACH ROW BEGIN
  -- You can reference OLD.column_name and NEW.column_name here
END;

5

If you encounter the error, carefully examine which trigger is failing and verify that the `OLD` and `NEW` keywords are being used in accordance with the trigger event type (INSERT, DELETE, UPDATE).

2. Dropping and Recreating Faulty Triggers medium

Remove and re-add the trigger to resolve syntax or logical issues.

1

Identify the specific trigger that is causing the 'Invalid Trigger Row Reference' error. You can usually find this by looking at the error message itself or by inspecting your database schema.

2

Obtain the `CREATE TRIGGER` statement for the problematic trigger. This is essential for recreating it correctly.

SHOW CREATE TRIGGER trigger_name;

3

Drop the existing trigger.

DROP TRIGGER trigger_name;

4

Carefully review the `CREATE TRIGGER` statement you obtained in step 2. Correct any syntax errors, particularly in how `OLD` and `NEW` are used, ensuring they align with the trigger event (INSERT, DELETE, UPDATE).

5

Recreate the trigger with the corrected `CREATE TRIGGER` statement.

-- Paste your corrected CREATE TRIGGER statement here
CREATE TRIGGER trigger_name ...

3. Verify Trigger Context and Event Types medium

Ensure triggers are defined for the correct events and that row references are appropriate.

1

List all triggers in your database to identify potential candidates for the error.

SHOW TRIGGERS;

2

For each trigger, examine its definition, paying close attention to the `ON` clause (the table it's associated with) and the trigger event (`BEFORE INSERT`, `AFTER INSERT`, `BEFORE DELETE`, `AFTER DELETE`, `BEFORE UPDATE`, `AFTER UPDATE`).

3

Check the trigger body for references to `OLD` and `NEW` row values. Confirm that these references are valid for the trigger's event type:

/*
For INSERT triggers: NEW is valid, OLD is not.
For DELETE triggers: OLD is valid, NEW is not.
For UPDATE triggers: BOTH OLD and NEW are valid.
*/

4

If you find an invalid reference (e.g., using `OLD` in an `INSERT` trigger, or `NEW` in a `DELETE` trigger), correct the trigger definition. This might involve removing the invalid reference or changing the trigger event type if the logic requires it.

5

After making corrections, re-apply the trigger definitions.

🔗

Related Errors

5 related errors

MariaDB Error 1363: Invalid Trigger Row Reference