更新时的 MySQL 触发器不将小时数据发送到统计表

Question

我有一个 MySQL 触发器，可以根据正在运行的 UPDATE 查询将小时数更新到统计表中。

触发器是:

DELIMITER //

CREATE TRIGGER update_stats AFTER UPDATE ON user_hours 

FOR EACH ROW

BEGIN

  IF NEW.hours_committed = 'completed' THEN


    INSERT INTO hours_statistics (user_id, opportunity_id, completed_hours) VALUES

      (OLD.user_id, OLD.opportunity_id, -OLD.completed_hours),

      (NEW.user_id, NEW.opportunity_id, +NEW.completed_hours)

    ON DUPLICATE KEY UPDATE

      completed_hours = completed_hours + VALUES(completed_hours);


  END IF;

END//

工作查询:

UPDATE user_hours JOIN user_calendar USING (user_calendar_id, opportunity_id)
SET    user_hours.completed_hours = agreed_hours,
       user_hours.hours_committed = 'completed'
WHERE  user_hours.hours_committed = 'accepted' 
   AND user_hours.completed_hours IS NULL
   AND user_calendar.date_start = CURRENT_DATE();

我有另一个与上面类似的查询，它更新同一个表，但有一点不同，即检查的 completed_hours 不为空。查询如下:

UPDATE user_hours JOIN user_calendar USING (user_calendar_id, opportunity_id)
SET    user_hours.hours_committed = 'completed'
WHERE  user_hours.hours_committed = 'accepted' 
   AND user_hours.completed_hours IS NOT NULL
   AND user_calendar.date_start = '2012-08-23'

上面触发了更新触发器，但是在统计表中，最后一次更新查询的 completed_hours 显示为 0。

任何对触发器有更好了解的人都可以帮助我吗？

谢谢，

标记

Answer 1

解决方案是:

DELIMITER //

CREATE TRIGGER update_stats AFTER UPDATE ON user_hours 

FOR EACH ROW

BEGIN

IF  NEW.hours_committed =  'completed'  THEN
IF OLD.completed_hours IS NULL THEN
BEGIN
INSERT INTO hours_statistics (user_id,opportunity_id,completed_hours)
VALUES
(OLD.user_id, OLD.opportunity_id, -OLD.completed_hours),
(NEW.user_id, NEW.opportunity_id, +NEW.completed_hours)
ON DUPLICATE KEY UPDATE
completed_hours = completed_hours + VALUES(completed_hours);
END;
ELSE
INSERT INTO hours_statistics (user_id,opportunity_id,completed_hours)
VALUES
(NEW.user_id, NEW.opportunity_id, +NEW.completed_hours)
ON DUPLICATE KEY UPDATE
completed_hours = completed_hours + VALUES(completed_hours);
END IF;
END  IF;


END//