mysql 更新时触发，保持列高于其他列

Question

我正在尝试创建一个 mySql 触发器。

表program_user看起来像这样:

用户 ID |节目 ID |周 |最大周 | is_active |

我的目标是保持 max_week 中的整数始终等于或高于 week因为用户可以重置周，所以我需要保持他的最佳进度。

行已创建并以 3 种不同的方式更新:

• 由服务器提供(实际周数+1)
• 由用户重置(周 =1)
• 由管理员设置

我在更新后尝试了多个代码来更新行本身，但它们似乎都不起作用。 Google 似乎没有找到任何类似的案例，也没有 stackOverflow :(

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `program_user` (
  `user_id` int(11) NOT NULL,
  `prog_id` int(11) NOT NULL,
  `week` int(11) DEFAULT NULL,
  `max_week` int(11) NOT NULL DEFAULT '1',
  `active` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `program_user` (`user_id`, `prog_id`, `week`, `max_week`, `active`) VALUES
(6967, 1, 2, 2, 1),
(6967, 2, 2, 4, 0),
(6967, 3, 1, 1, 0),
(6967, 4, 1, 1, 0),
(6967, 5, 1, 1, 0),
(6968, 1, 1, 1, 1),
(6968, 2, 1, 1, 0),
(6968, 3, 1, 1, 0),
(6968, 4, 1, 1, 0),
(6968, 5, 1, 1, 0);

DELIMITER $$
CREATE TRIGGER `trigg_max_week1` BEFORE UPDATE ON `program_user` FOR EACH ROW BEGIN
       IF new.week >= old.max_week THEN
          SET new.max_week = new.week;
       END IF;
    END
$$
DELIMITER ;

ALTER TABLE `program_user`
  ADD UNIQUE KEY `user_idpk` (`user_id`,`prog_id`);


COMMIT;

然后更新:

UPDATE `program_user` SET `week` = '5' WHERE `program_user`.`user_id` = 6967 AND `program_user`.`prog_id` = 2;

结果:保存触发器时没有错误，更新任何周列时也没有错误，但是更新后列 max_week 保持其初始状态。

谢谢:)

Answer 1

我看不到您的问题，您的触发器按照所提供的信息按预期工作。

drop table if exists program_user;
CREATE TABLE `program_user` (
  `user_id` int(11) NOT NULL,
  `prog_id` int(11) NOT NULL,
  `week` int(11) DEFAULT NULL,
  `max_week` int(11) NOT NULL DEFAULT '1',
  `active` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `program_user` (`user_id`, `prog_id`, `week`, `max_week`, `active`) VALUES
(6967, 1, 2, 2, 1),
(6967, 2, 2, 4, 0),
(6967, 3, 1, 1, 0),
(6967, 4, 1, 1, 0),
(6967, 5, 1, 1, 0),
(6968, 1, 1, 1, 1),
(6968, 2, 1, 1, 0),
(6968, 3, 1, 1, 0),
(6968, 4, 1, 1, 0),
(6968, 5, 1, 1, 0);

DELIMITER $$
CREATE TRIGGER `trigg_max_week1` BEFORE UPDATE ON `program_user` FOR EACH ROW 
BEGIN
       IF new.week >= old.max_week THEN
          SET new.max_week = new.week;
       END IF;
END $$
DELIMITER ;

ALTER TABLE `program_user`
  ADD UNIQUE KEY `user_idpk` (`user_id`,`prog_id`),
  ADD KEY `const_seance_id_seance` (`prog_id`);


UPDATE `program_user` 
    SET `week` = '5' 
WHERE `program_user`.`user_id` = 6967 AND `program_user`.`prog_id` = 2;

select * from program_user WHERE `program_user`.`user_id` = 6967 AND `program_user`.`prog_id` = 2;

+---------+---------+------+----------+--------+
| user_id | prog_id | week | max_week | active |
+---------+---------+------+----------+--------+
|    6967 |       2 |    5 |        5 |      0 |
+---------+---------+------+----------+--------+
1 row in set (0.00 sec)