1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
David Hernandez
  • 11
  • 1
  • You must not insert new rows for table A inside an update trigger on that same table A – derpirscher Apr 28 '21 at 06:53
  • Thank you for the reply Michael. I am a newbie and got "inspired" to create my sample code from this example mysqltutorial.org/create-the-first-trigger-in-mysql.aspx . It seems it may worked by inserting into the same table. Per your reply, would it be possible to insert into a different table i.e. INSERT INTO ArgentineWines2 ?. Or is there any other way to correct/fix something inside the code I presented. I tried to make a link for the URL but it did not work, sorry. I am stuck at this stage in my learning tutorial, stuck for about a week. Your feedback will be highly appreciated. D.H – David Hernandez Apr 28 '21 at 16:02

0 Answers0