mysql - 使用MySQL触发器插入或更新行

我有两个MySQL表：sms和consumption。 sms表包含所有事务，而consumption表包含消耗的短信总数。

我的目标是现在能够计算一天的实际消耗量，因此我只需要直接检查consumption表即可。
为此，我正在尝试创建一个触发器来验证：


如果对于第1天，consumption表中没有行，则插入新行
如果第一天已经有一行，请更新它


在这里，我的代码不起作用。

CREATE TRIGGER tg_new_sms_sent
AFTER INSERT ON sms
FOR EACH ROW
    SET @k = (SELECT id FROM consumption WHERE period = NEW.simple_date);
    CASE
        WHEN ISNULL(@k) THEN
            INSERT INTO consumption (system_id, period, credit, sms)
            VALUES (NEW.system_id, NEW.simple_date, NEW.used_credit, NEW.sms_count);
        ELSE
            UPDATE consumption SET credit = credit + New.used_credit, sms = sms + NEW.sms_count WHERE (system_id = NEW.system_id) AND (period = NEW.simple_date);
    END CASE

我能怎么做？

最佳答案

试试下面的代码。

DELIMITER $$
CREATE TRIGGER tg_new_sms_sent
AFTER INSERT ON sms
FOR EACH ROW
  BEGIN
    DECLARE num_rows INT;

    SELECT COUNT(id) INTO num_rows FROM consumption
    WHERE system_id = NEW.system_id AND period = NEW.simple_date;

    IF num_rows = 0 THEN
        INSERT INTO consumption (system_id, period, credit, sms)
        VALUES (NEW.system_id, NEW.simple_date, NEW.used_credit, NEW.sms_count);
    ELSE
        UPDATE consumption
        SET credit = credit + NEW.used_credit, sms = sms + NEW.sms_count
        WHERE system_id = NEW.system_id AND period = NEW.simple_date;
    END IF;

  END$$
DELIMITER ;