CODEWITHSUNDEEP
mysqlsqlphpmyadmin
AmbrosiaDevelopments
AmbrosiaDevelopments

Reputation: 2592

mySQL trigger cannot update table already in use by the statement that invoked the trigger

After creating either of the triggers below I get the error (detailed after the code) when I try to do an update. No matter which way I go I seem to be unable to update the table. Do I have to create a new table and put one column in that for updating or is there a way around this? This page says: "Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger." But then what's the point of the NEW and OLD keywords? Am I just using them incorrectly?

table: gallery

columns: weight, modifier, rating

trigger: updateRating

CREATE TRIGGER updateRating
BEFORE UPDATE ON gallery
FOR EACH ROW
UPDATE gallery SET rating= sum(NEW.weight * NEW.modifier)

or

CREATE TRIGGER updateRating
AFTER UPDATE ON gallery
FOR EACH ROW
UPDATE gallery SET rating= sum(weight * modifier)

SQL query: Edit

UPDATE `acs`.`gallery` SET `weight` = '6' WHERE `gallery`.`id` =1 LIMIT 1

MySQL said: Documentation

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

Upvotes: 1

Views: 6684

Answers (1)

Devart
Devart

Reputation: 122002

Right, error means that you cannot modify gallery table because the trigger was defined with this table. There is one possible way to update the value, it is a BEFORE UPDATE trigger, here it is an example - 

CREATE TRIGGER updateRating
BEFORE UPDATE
ON gallery
FOR EACH ROW
BEGIN
  SET NEW.rating= NEW.weight * NEW.modifier; -- You may update a NEW value.
END

At the end, let me ask a question. Why are you going to use a trigger to modify the rating? I want to say that you can calculate rating on the fly, in SELECT statement.

Upvotes: 5

Related Questions