MySQL UPDATE trigger error - can't update table already used by statement

Question

I'm trying to set a MySQL trigger that fires when rows in a table are updated. When it occurs, it checks to see the new value of one column and then sets a different column accordingly. When I test it, I get an error:

1442 - Can't update table 'opportunities2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Here's my trigger:

BEGIN
    IF NEW.opportunityOwnerRole <> OLD.opportunityOwnerRole AND 
    NEW.opportunityOwnerRole LIKE '%one%'
    THEN 
        UPDATE opportunities2 SET NEW.opportunityProduct = 'one' 
        WHERE rowId = NEW.rowId;
    ELSEIF NEW.opportunityOwnerRole <> OLD.opportunityOwnerRole AND 
    NEW.opportunityOwnerRole NOT LIKE '%two%' 
    THEN
        UPDATE opportunities2 SET NEW.opportunityProduct = 'two'         WHERE rowId = NEW.rowId; 
    END IF; 
END

Any ideas? Thanks!

EDIT: correct answer below, but wanted to post the fixed trigger if anyone needs it:

BEGIN
    IF NEW.opportunityOwnerRole <> OLD.opportunityOwnerRole AND 
    NEW.opportunityOwnerRole LIKE '%one%'
    THEN 
        SET NEW.opportunityProduct = 'one';
    ELSEIF NEW.opportunityOwnerRole <> OLD.opportunityOwnerRole AND 
    NEW.opportunityOwnerRole NOT LIKE '%two%' 
    THEN
        SET NEW.opportunityProduct = 'two';
    END IF; 
END

Please add the `Create Trigger` statement. That's where the issue lies in my opinion. — Harshil Doshi, Feb 16 '19 at 20:09
@HarshilDoshi i failed to mention i'm setting this up in phpmyadmin so that's being inserted automatically — Matt, Feb 16 '19 at 20:20

score 2 · Accepted Answer · answered Feb 16 '19 at 20:14

A trigger can’t fire update or insert statements into a table that invoked the trigger action. What you can do is to manipulate update or insert statements within the trigger code (BEFORE triggers)

E.g.

IF NEW.opportunityRole <> OLD.opportunityRole
THEN
     SET NEW.opportunityRole = 'one';
END IF

MySQL UPDATE trigger error - can't update table already used by statement

1 Answers1