更新后的MySQL，更新同一表的datetime列 [英] MySQL after update, update datetime column of same table

问题描述

我有一个名为 teamMembers 的表，该表存储一个人的姓名和电子邮件地址:

I have a table called teamMembers, this table stores a name and an e-mail address of a person:

id       INT (UNIQUE, AUTOINCREMENT)
name     VARCHAR(255)
email    VARCHAR(255)
updated  DATETIME
created  TIMESTAMP

我已经使用 phpMyAdmin 创建了触发器，并且在此导出中看起来像这样:

I've created my trigger using phpMyAdmin and it looks like this in this export:

CREATE TRIGGER teamMembers.updated_updater 
AFTER UPDATE ON teamMembers
    FOR EACH ROW 
        BEGIN
            UPDATE teamMembers SET updated = NOW() WHERE id = OLD.id;
        END

因此，当我使用这样的查询时: UPDATE teamMembers SET名称='test'，ID = 1 我应该将更新的列设置为当前日期时间.

So when I use a query like this: UPDATE teamMembers SET name = 'test' WHERE id = 1 I should set the updated column to the current datetime.

这不起作用，我收到此错误消息:

This is not working though, I get this error message:

#1442-无法更新存储的函数/触发器中的表'teamMembers'，因为调用该存储的语句已使用该表功能/触发.

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

我真的无法弄清楚问题出在哪里.我认为这是因为此触发器也会调用自身，因此将成为一个无限循环.我认为必须有一种方法可以自动更新刚刚更新的记录，而不会更改我的查询.

I cant really figure out what the problem is here. I think it's because this trigger would invoke itself as well and would therefore become an endless loop. I think there must be a way to auto-update a record that has just been updated without altering my query.

有人可以帮助我使用MySQL触发器更新到 updated 列吗?

Could someone help me to use a MySQL trigger to update to updated column?

推荐答案

使用之前触发器:

CREATE TRIGGER teamMembers.updated_updater 
BEFORE UPDATE ON teamMembers
    FOR EACH ROW 
BEGIN
    SET new.updated = NOW();
END

这篇关于更新后的MySQL，更新同一表的datetime列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！