Mysql在更新保留的列子容量时触发if语句

Question

我想设置检查类(class)容量，如果是全类应该是 确认。当我更新保留时它给出错误。请帮忙。谢谢。

CREATE TRIGGER `control_class_capacity` 
AFTER UPDATE ON  `learningcenter_class`
FOR EACH ROW BEGIN

IF NEW.capacity - NEW.reserved = 0 THEN BEGIN

UPDATE learningcenter_class SET isconfirm = 1 WHERE class_id = NEW.class_id;

END; END IF;
END

Answer 1

假设您的 class_id 是唯一的，您可以将触发器更改为之前触发器并设置 NEW.ISCONFIRM。顺便说一句，MYSQL 中的 if 语句不需要 begin 和 end 语句。

drop table if exists t;
create table t(class_id int ,capacity int, reserved int, isconfirm int);
insert into t values (1,1,0,0) , (2,1,0,0);

drop trigger if exists `control_class_capacity`;
delimiter $$
CREATE TRIGGER `control_class_capacity` 
before UPDATE ON  t
FOR EACH ROW BEGIN

IF NEW.capacity - NEW.reserved = 0 THEN

SET new.isconfirm = 1;

END IF;
END $$

delimiter ;

update t set reserved = 1 where class_id = 1;

select * from t;

+----------+----------+----------+-----------+
| class_id | capacity | reserved | isconfirm |
+----------+----------+----------+-----------+
|        1 |        1 |        1 |         1 |
|        2 |        1 |        0 |         0 |
+----------+----------+----------+-----------+
2 rows in set (0.00 sec)