MySQL触发器-使用从另一个表中选择的值更新表 [英] MySQL Trigger - update table with value selected from another table

问题描述

我在用MySQL触发器的另一个表中选择的值更新表中的行时遇到问题.我的触发器看起来像这样

I'm having problems updating row in a table with value selected from another table in MySQL Trigger. My Trigger looks like this

CREATE TRIGGER update_user_last_login
    AFTER INSERT or UPDATE ON last FOR EACH ROW
    BEGIN 
        DECLARE _user_id INTEGER;
        SELECT user_id INTO _user_id FROM user_profile WHERE user_name = NEW.username;
        UPDATE user set last_login = NEW.seconds WHERE id = _user_id;
    END 

我收到错误消息:

ERROR 1054 (42S22): Unknown column '_user_id' in 'where clause'

有人可以指出我正确的方向吗?

Could somebody point me to the right direction please?

非常感谢你， 米兰.

推荐答案

这是复合触发事件(INSERT or UPDATE)上的语法错误.试试:

This is a syntax error on the compound trigger event (INSERT or UPDATE). Try:

CREATE TRIGGER update_user_last_login
    AFTER UPDATE ON last FOR EACH ROW ...

我不认为 mysql在同一触发器中支持复合事件.您可以创建两个触发器，一个用于插入后，另一个用于更新后.这两个触发器可以重复调用相同的代码，也可以调用通用的存储过程.

I don't think mysql supports compound events in the same trigger. You could create two triggers, one for after insert and one for after update. Those two triggers can call the same code in duplicate or call a common stored procedure.

这篇关于MySQL触发器-使用从另一个表中选择的值更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！