MySQL Forums
Forum List  »  MySQL Workbench

Error Code: 1442. Can't update table tablename in stored function/trigger because it is already used by statement which invoked this stored function
Posted by: David Hernandez
Date: April 24, 2021 04:26PM

I created a simple table 'ArgentinianWines' w/5 fields and one primary key. I loaded the table w/10 records using'LOAD DATA INFILE 'C:\ArgentineWines.csv'. No problem so far. Then a Store Procedure was created and executed perfectly, as follows:

DROP TRIGGER IF EXISTS Sakila.Trigger_Before_An_ArgentineWines_update;
CREATE TRIGGER Trigger_Before_An_ArgentineWines_update
BEFORE 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 rises 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

-----------1st attempt:--------------
Use Sakila;
UPDATE ArgentineWines
SET WineName = REPLACE(WineName,'GRAFFIGNA RESERVE','GRAFFIGNA RESERVE Grandiose')
WHERE RankingId = 6;

-----------2nd attempt:--------------
Use Sakila;
UPDATE ArgentineWines
SET WineName = 'GRAFFIGNA RESERVE Grandiose'
WHERE RankingId = 6;


No matter what I try, it generates 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."

But which other statement is invoking my store 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

'

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.