我正在创建以下测试触发器,以便在发生UPDATE
操作时更新字段的值:
CREATE TRIGGER `test_index`
AFTER UPDATE ON `main_itemmaster`
FOR EACH ROW
UPDATE `main_itemmaster` SET `guid` = "hello" WHERE `id` = NEW.id;
但是,每当我对此表进行更新时,都会收到以下错误:
Can't update table 'main_itemmaster' in stored function/trigger
because it is already used by statement which invoked this stored function/trigger.
为什么会发生这种情况,我将如何解决这个问题?
你想做什么?
显然,您无法在更新时更新同一表...它变得循环。
是否要在插入时更新?这应该是可能的:
CREATE TRIGGER `test_index`
AFTER INSERT ON `main_itemmaster`
FOR EACH ROW
UPDATE `main_itemmaster` SET `guid` = "hello" WHERE `id` = NEW.id;
我看到了你的评论。我提出这个建议:
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 ;
我测试了它,它有效!此外,如果您只更新一个字段。
问候