Correct mysql trigger syntax to prevent infinite loop

Question

I am creating the following test trigger to update the value of a field whenever an UPDATE operation occurs:

CREATE TRIGGER `test_index` 
AFTER UPDATE ON `main_itemmaster` 
FOR EACH ROW  
    UPDATE `main_itemmaster` SET `guid` = "hello" WHERE `id` = NEW.id;

However, whenever I do an update to this table I get the following error:

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

Why is this occurring and how would I fix this?

White Feather · Accepted Answer · 2016-03-18 23:06:05Z

3

What do you want to do?

Clearly you cannot update same table on updating... It become circular.

Do you want to update on insert? That should be possible:

CREATE TRIGGER `test_index` 
AFTER INSERT ON `main_itemmaster` 
FOR EACH ROW  
UPDATE `main_itemmaster` SET `guid` = "hello" WHERE `id` = NEW.id;

I saw your comment. I propose this:

delimiter //
CREATE TRIGGER test_index BEFORE UPDATE ON main_itemmaster
FOR EACH ROW
    BEGIN
    IF NEW.name <> OLD.name OR NEW.location<>OLD.location THEN
           SET NEW.guid = concat(NEW.name, NEW.location);
       END IF;
   END;//
delimiter ;

I tested it and it works! Also if you update only one field.

Regards

edited Mar 18, 2016 at 23:06

answered Mar 18, 2016 at 22:04

White Feather

2,7931 gold badge17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David542 Over a year ago

Thanks for the answer. Here is what I'm trying to accomplish. Let's say I have three fields, name+location+guid, and whenever I update the location or name of a row, I also want the guid to update to concat(name, location). How would I do that?

White Feather Over a year ago

See updated answer (I forgot to put concat(NEW.name, NEW.location)). I tested it and it works.

Collectives™ on Stack Overflow

Correct mysql trigger syntax to prevent infinite loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related