mysql - 触发器不工作 :

Question

我在表 PENDING 上创建了触发器。待处理表有 3 列 - uniqueId、duration 和 maxDuration。我有另一个表 COUNT，有 2 列 - req_id，总计

这是我的触发器--

CREATE TRIGGER plus3second BEFORE INSERT
ON PENDING
FOR EACH ROW
BEGIN
DECLARE req_id varchar(25);
DECLARE total int(11);
DECLARE duration int(2);

SET req_id = SUBSTR(new.uniqueId, 1, 14);

Select total into total from COUNT where req_id = 'req_id';

IF total > 100 THEN

 SET duration = new.duration + 3;

  IF duration < new.maxDuration Then
     SET new.duration = duration;
  END IF;


END IF;


END

触发器创建成功。我在 COUNT 和 PENDING 上触发了这些查询-

insert into COUNT values ('77711422099653',200);
insert into PENDING (uniqueId, duration, maxDuration) values ('77711422099653919893277163', 3, 20);

但是触发器不起作用......问题出在哪里？

Answer 1

检查此触发器定义(名称冲突较少):

CREATE TRIGGER plus3second
   BEFORE INSERT
   ON PENDING
   FOR EACH ROW
BEGIN
   DECLARE tReqID     varchar(25);
   DECLARE tTotal      int(11);
   DECLARE tDuration   int(2);

   SET tReqID = SUBSTR(new.uniqueId, 1, 14);

   SELECT total
     INTO tTotal
     FROM COUNT
    WHERE req_id = tReqID;

   IF tTotal > 100
   THEN
      SET tDuration = new.duration + 3;

      IF tDuration < new.maxDuration
      THEN
         SET new.duration = tDuration;
      END IF;
   END IF;
END