Reputation: 11
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