David Hernandez
David Hernandez

Reputation: 11

Error Code: 1442. Can't update table 'ArgentineWine' in stored function/trigger because it is already used by statement

I have read the 4 cases in this forum reg.

Error Code: 1442. Can't update table 'tablename' in stored function/trigger because it is already used by statement

Those cases are way more complex than my situation. I created an ArgentineWines table with 5 columns, loaded 10 rows, and created a trigger:

CREATE TRIGGER Trigger_Before_An_ArgentineWines_update 
AFTER UPDATE ON ArgentineWines
FOR EACH ROW 
    INSERT INTO ArgentineWines
    SET action = 'update',
        RankingId = OLD.RankingId,
        WineName = OLD.WineName,
        Description = OLD.Description,
        RecordDate = NOW();

The problem arises when I try to test out the trigger by making a change to the table. I attempted to update the table in two different ways

First attempt:

USE Sakila;

UPDATE ArgentineWines
SET WineName = REPLACE(WineName,'GRAFFIGNA RESERVE','GRAFFIGNA RESERVE Grandiose')
WHERE RankingId = 6;

Second attempt:

USE Sakila;

UPDATE ArgentineWines
SET WineName = 'GRAFFIGNA RESERVE Grandiose'
WHERE RankingId = 6;

No matter what I try, I get this error:

Error Code: 1442. Can't update table 'argentinewines' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

I have used BEFORE I have used AFTER, no matter what the error keeps popping up.

Questions: which other statement is invoking my stored procedure? What is already using what? The error description is not that clear.

Please, any thoughts or a solution to this mystery will be great. DH

Upvotes: 1

Views: 111

Answers (0)

Related Questions