📋

Description

This error indicates that a database trigger is attempting to modify a row value that it is not permitted to change within its current context. It commonly occurs when a trigger tries to update `OLD` row values, which are read-only, or when it tries to modify `NEW` values in a way that conflicts with MariaDB's trigger execution rules.

💬

Error Message

Updating of %s row is not allowed in %strigger

🔍

Known Causes

3 known causes

⚠️

Attempting to Modify OLD Row Values

Triggers can access `OLD` values for rows (e.g., `OLD.column_name`), but these values are always read-only. Any attempt to change an `OLD` value will result in this error.

⚠️

Incorrect NEW Row Value Modification

While `BEFORE INSERT` and `BEFORE UPDATE` triggers can modify `NEW` row values, certain modifications might be disallowed, especially if they create a recursive or invalid state for the transaction.

⚠️

Trigger Type Misunderstanding

Using `OLD` values in `BEFORE INSERT` triggers or attempting to modify `NEW` values in `AFTER` triggers can lead to this error due to a misunderstanding of trigger execution phases and mutable/immutable row states.

🛠️

Solutions

3 solutions available

1. Modify Trigger Logic to Avoid Direct Row Updates medium

Rewrite the trigger to use indirect methods like updating a separate audit table or setting flags instead of directly modifying the row that fired the trigger.

1

Identify the specific table and column being updated within the trigger that causes the error. Examine the `CREATE TRIGGER` statement or use `SHOW CREATE TRIGGER trigger_name;` to understand the trigger's logic.

SHOW CREATE TRIGGER my_trigger_name;

2

If the trigger is attempting to update the same row that is being modified (either `OLD` or `NEW` row within the trigger context), you need to refactor.

-- Example of what NOT to do within a trigger:
-- UPDATE my_table SET some_column = 'new_value' WHERE id = NEW.id;

3

Consider alternative approaches. For auditing, log changes to a separate audit table. For enforcing business rules, use `SIGNAL SQLSTATE` to raise an error or update a different related table.

-- Example of logging to an audit table:
INSERT INTO audit_log (table_name, record_id, action, timestamp) VALUES ('my_table', NEW.id, 'UPDATE', NOW());

4

Drop the existing trigger and recreate it with the revised logic.

DROP TRIGGER IF EXISTS my_trigger_name;
CREATE TRIGGER my_trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
    -- New, indirect logic here
    INSERT INTO audit_log (table_name, record_id, action, timestamp) VALUES ('my_table', NEW.id, 'UPDATE', NOW());
END;

2. Temporarily Disable Trigger for Specific Operations easy

For controlled scenarios, you can temporarily disable the trigger, perform the operation, and then re-enable it.

1

Understand which trigger is causing the issue. The error message `Updating of %s row is not allowed in %strigger` should provide hints about the table and trigger name.

SHOW TRIGGERS LIKE '%my_table%';

2

Temporarily disable the trigger. This is done by dropping it and then recreating it with `DISABLE` specified (though MariaDB doesn't directly support `DISABLE` in the `CREATE TRIGGER` syntax like some other RDBMS. Instead, you'd typically remove and re-add, or use a conditional logic within the trigger itself). A more direct approach is to drop and recreate it later.

DROP TRIGGER IF EXISTS my_trigger_name;

3

Perform the operation that was failing due to the trigger.

-- Your UPDATE, INSERT, or DELETE statement here
UPDATE my_table SET ... WHERE ...;

4

Recreate the trigger with its original logic. If the trigger's logic is inherently problematic, this is a temporary workaround and not a permanent fix. You should still aim for Solution 1.

CREATE TRIGGER my_trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
    -- Original trigger logic here that caused the error
END;

3. Implement Conditional Logic Within the Trigger medium

Add checks within the trigger to prevent it from attempting to update the row that fired the trigger.

1

Examine the trigger's code to locate the problematic update statement.

SHOW CREATE TRIGGER my_trigger_name;

2

Analyze the conditions under which the problematic update occurs. The error typically arises when `OLD.column = NEW.column` or similar conditions lead to an update on the same row.

-- Example of a problematic update within a trigger:
IF OLD.status <> NEW.status THEN
    UPDATE my_table SET last_modified = NOW() WHERE id = NEW.id;
END IF;

3

Add explicit checks to prevent self-modification. For instance, if the trigger is designed to update a `last_modified` timestamp, ensure it doesn't trigger itself.

CREATE TRIGGER my_trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        -- Only update if the row is actually changing in a way that shouldn't trigger itself again
        -- Or, if the intention is to update a timestamp, ensure the timestamp itself isn't what's triggering the update in a loop.
        -- A common scenario is updating a `version` or `modified_at` column.
        -- If the trigger is meant to update a timestamp, and the timestamp itself is part of the update condition,
        -- you might need to ensure the timestamp column is not part of the `NEW` values that are checked.
        -- For example, if `modified_at` is being updated, and the trigger is `BEFORE UPDATE` on `modified_at`:
        -- This is a simplified example, real-world scenarios might be more complex.
        -- To prevent infinite loops, ensure the conditions for the update are not met by the update itself.
        -- For example, if you're updating a `version` column, and the trigger is fired by `version` changing:
        IF OLD.version = NEW.version THEN
            -- Do nothing, or handle differently
        ELSE
            -- Proceed with update
            UPDATE my_table SET last_modified = NOW() WHERE id = NEW.id;
        END IF;
    END IF;
END;

4

Drop the existing trigger and recreate it with the added conditional logic.

DROP TRIGGER IF EXISTS my_trigger_name;
-- Recreate with the conditional logic as shown above
CREATE TRIGGER my_trigger_name
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
    -- Your conditional logic here
END;

🔗

Related Errors

5 related errors

MariaDB Error 1362: Trigger Row Update Restriction