mysql - SELECT SUM(column) INTO @variable in MySQL Trigger

Question

SELECT SUM(column) INTO @variable FROM tabl2 WHERE column2 = NEW.column in MySQL 更新触发器返回 NULL。

我的代码如下

CREATE TRIGGER `users_update` AFTER UPDATE ON `users`
 FOR EACH ROW BEGIN
    DECLARE profit DOUBLE DEFAULT 0;

    SELECT SUM(PROFIT) INTO @profit FROM trades WHERE LOGIN = new.LOGIN AND PROFIT > 0;

    UPDATE user_details SET profit = @profit WHERE LOGIN = new.LOGIN;
END;

然而，当单独执行相同的查询时，我得到了数据。这真让我抓狂。有人可以建议我哪里出错了吗？

Answer 1

尝试其中之一:

CREATE TRIGGER `users_update` AFTER UPDATE ON `users`
 FOR EACH ROW BEGIN
    DECLARE profit DOUBLE DEFAULT 0;

    SELECT @profit:=SUM(PROFIT) FROM trades WHERE LOGIN = new.LOGIN AND PROFIT > 0;

    UPDATE user_details SET profit = @profit WHERE LOGIN = new.LOGIN;
END;

Or if you want to use into:

CREATE TRIGGER `users_update` AFTER UPDATE ON `users`
 FOR EACH ROW BEGIN
    DECLARE profit DOUBLE DEFAULT 0;

    SELECT SUM(PROFIT) FROM trades WHERE LOGIN = new.LOGIN AND PROFIT > 0 INTO @profit;

    UPDATE user_details SET profit = @profit WHERE LOGIN = new.LOGIN;
END;