英文:
MySQL update table column by after update trigger
问题
我想在MySQL中，在更新其他表的列后更新`_history`列。
我创建了以下触发器：
CREATE DEFINER=`root`@`localhost` TRIGGER `update_history` AFTER
UPDATE
ON
`_users` FOR EACH ROW
BEGIN
IF NEW.is_active <> OLD.is_active || NEW.is_deleted <> OLD.is_deleted THEN
UPDATE
`_users`
SET
NEW._history = JSON_ARRAY_APPEND(
OLD._history,
'$',
'{\"date\": \"' || cast(NOW() + 0 as CHAR) || '\",\"username\": \"' || OLD.username || '\",\"is_active\": \"' || cast(OLD.is_active as CHAR)|| '\",\"is_deleted\": \"' || cast(OLD.is_deleted as CHAR) || '\"}'
)
WHERE
NEW.user_id = OLD.user_id;
END IF;
END;
在更新`is_active`后，会发生以下错误。
SQL错误[4048] [42000]：INSERT或UPDATE语句中的赋值目标无效'NEW._history'。
在INSERT或UPDATE语句中，赋值目标'NEW._history'无效。
在INSERT或UPDATE语句中，赋值目标'NEW._history'无效。
我尝试过很多方法，比如使用`BEFORE UPDATE`或移除`IF`条件，但错误仍然发生。
英文:
I want to update _history column after other table's columns update in MySQL.
I create the below trigger:
CREATE DEFINER = `root` @`localhost` TRIGGER `update_history` AFTER
UPDATE
ON
`_users` FOR EACH ROW
BEGIN
IF NEW.is_active <> OLD.is_active || NEW.is_deleted <> OLD.is_deleted THEN
UPDATE
`_users`
SET
NEW._history = JSON_ARRAY_APPEND(
OLD._history,
'$',
'{' ||
'"date": "' || cast(NOW() + 0 as CHAR) || '"' || ',' ||
'"username": "' || OLD.username || '"' || ',' ||
'"is_active": "' || cast(OLD.is_active as CHAR)|| '"' || ',' ||
'"is_deleted": "' || cast(OLD.is_deleted as CHAR) || '"' ||
'}'
)
WHERE
NEW.user_id = OLD.user_id;
END IF;
END;
after update is_active, the below error occurs.
SQL Error [4048] [42000]: Invalid target for assignment in INSERT or UPDATE statement 'NEW._history'.
Invalid target for assignment in INSERT or UPDATE statement 'NEW._history'.
Invalid target for assignment in INSERT or UPDATE statement 'NEW._history'.
I tried many ways like using BEFORE UPDATE or removing the IF condition but the error still occurs.
答案1
得分: 2
在MySQL中，不能在触发器中更新同一张表，因为这会导致触发器执行的无限循环。
但您不必使用UPDATE，只需使用SET NEW.<column> = ...
CREATE DEFINER=`root`@`localhost` TRIGGER `update_history` BEFORE UPDATE
ON `_users` FOR EACH ROW
BEGIN
IF NEW.is_active <> OLD.is_active || NEW.is_deleted <> OLD.is_deleted THEN
SET
NEW._history = JSON_ARRAY_APPEND(
OLD._history,
'$', JSON_OBJECT(
'date', CAST(NOW() + 0 AS CHAR),
'username', OLD.username,
'is_active', CAST(OLD.is_active AS CHAR),
'is_deleted', CAST(OLD.is_deleted AS CHAR)
)
);
END IF;
END
我还展示了使用JSON_OBJECT()来替代使用字符串连接的容易出错的方法。
英文:
In MySQL, you can't update a table in a trigger for the same table, because it would cause an infinite loop of trigger execution.
But you don't have to use UPDATE. Just use SET NEW.<column> = ...
CREATE DEFINER = `root` @`localhost` TRIGGER `update_history` BEFORE UPDATE
ON `_users` FOR EACH ROW
BEGIN
IF NEW.is_active <> OLD.is_active || NEW.is_deleted <> OLD.is_deleted THEN
SET
NEW._history = JSON_ARRAY_APPEND(
OLD._history,
'$', JSON_OBJECT(
'date', CAST(NOW() + 0 AS CHAR),
'username', OLD.username,
'is_active', CAST(OLD.is_active AS CHAR),
'is_deleted', CAST(OLD.is_deleted AS CHAR)
)
);
END IF;
END
I also showed the use of JSON_OBJECT() instead of the error-prone method of formatting JSON using string-concatenation.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论