📋

Description

MySQL Error 1363 indicates that a trigger is attempting to access a `NEW` or `OLD` row alias that does not exist in the current context of the DML operation. This error specifically occurs when a trigger tries to reference a row that isn't applicable to the event (e.g., accessing an `OLD` row during an `INSERT` operation).

💬

Error Message

There is no %s row in %s trigger

🔍

Known Causes

3 known causes

⚠️

Accessing OLD row in INSERT trigger

An `INSERT` trigger attempts to reference the `OLD` row, which does not exist for newly inserted records.

⚠️

Accessing NEW row in DELETE trigger

A `DELETE` trigger tries to reference the `NEW` row, which is not available for records being deleted.

⚠️

Row variables in STATEMENT trigger

A trigger defined with `FOR EACH STATEMENT` attempts to use `NEW` or `OLD` row references, which are only valid for row-level triggers (`FOR EACH ROW`).

🛠️

Solutions

3 solutions available

1. Correctly Identify Row Context in Trigger easy

Ensure your trigger logic correctly refers to the `NEW` or `OLD` row pseudo-tables.

1

Review your trigger definition. The error 'Invalid Row Reference' often means you're trying to access a row that doesn't exist in the current trigger context. For `INSERT` and `UPDATE` triggers, you can use `NEW.column_name` to refer to the new row's values. For `UPDATE` and `DELETE` triggers, you can use `OLD.column_name` to refer to the old row's values.

-- Example for an INSERT trigger:
CREATE TRIGGER my_insert_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
    -- Accessing the new row's values
    INSERT INTO audit_log (column1_value, column2_value)
    VALUES (NEW.column1, NEW.column2);
END;

2

If you're attempting to reference both `OLD` and `NEW` in a trigger type that doesn't support it (e.g., `OLD` in an `INSERT` trigger), you'll encounter this error. Adjust your trigger to only use the appropriate pseudo-table for the event.

-- Incorrect usage for INSERT trigger:
-- This will cause error 1363
-- CREATE TRIGGER my_bad_trigger
-- AFTER INSERT ON my_table
-- FOR EACH ROW
-- BEGIN
--     IF OLD.some_column = 'value' THEN ... END IF;
-- END;

2. Recreate the Trigger medium

Drop and recreate the problematic trigger to ensure a clean definition.

1

Identify the exact name of the trigger causing the error. You can find this by querying the `information_schema.TRIGGERS` table.

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name';

2

Drop the identified trigger.

DROP TRIGGER IF EXISTS your_trigger_name;

3

Recreate the trigger with the correct syntax and logic, ensuring `NEW` and `OLD` are used appropriately for the trigger event type (`INSERT`, `UPDATE`, `DELETE`).

-- Replace with your actual trigger definition
CREATE TRIGGER your_trigger_name
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    -- Your trigger logic here, using NEW.column_name
    -- For example:
    -- INSERT INTO another_table (id, value) VALUES (NEW.id, NEW.value);
END;

3. Verify Trigger Event Type and Table Name medium

Ensure the trigger is defined for the correct event and table, and that the table exists.

1

Check the `EVENT_MANIPULATION` and `EVENT_OBJECT_TABLE` columns in `information_schema.TRIGGERS` for the trigger in question. This will confirm if the trigger is associated with the correct event (e.g., `INSERT`, `UPDATE`, `DELETE`) and table.

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = 'your_trigger_name' AND TRIGGER_SCHEMA = 'your_database_name';

2

Confirm that the `EVENT_OBJECT_TABLE` specified in the trigger definition actually exists in your database.

SHOW TABLES LIKE 'your_table_name';

3

If the table name is incorrect or the table doesn't exist, you'll need to correct the trigger definition or create the table before recreating the trigger.

-- Example: If table name was wrong, correct it and recreate
DROP TRIGGER IF EXISTS your_trigger_name;
CREATE TRIGGER your_trigger_name
AFTER INSERT ON correct_table_name
FOR EACH ROW
BEGIN
    -- ... trigger logic ...
END;

🔗

Related Errors

5 related errors

MySQL Error 1363: Invalid Row Reference in Trigger