set foreign_key_checks = 0;

create table dims.buildings(
    BuildingID int(10) not null primary key comment "楼栋编号",
    BName varchar(50) not null comment "楼栋名称",
    BFloorNumber int(20) not null comment "楼层数量",
    BManager varchar(50) not null comment "管理员姓名",
    BMPhone varchar(15) not null comment "管理员电话"
) engine = InnoDB default charset utf8 comment "楼栋表";

set foreign_key_checks = 1;

use dims;
DELIMITER $$
CREATE TRIGGER check_floornumber  # 设置触发器，1，2号楼只有8层楼；3，4号楼只有9层；5号楼只有11层
BEFORE INSERT ON dims.buildings FOR EACH ROW
BEGIN
    IF NEW.BuildingID = 1 AND NEW.BFloorNumber > 8 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The Number of Floors is larger than 8';
    END IF;
    IF NEW.BuildingID = 2 AND NEW.BFloorNumber > 8 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The Number of Floors is larger than 8';
    END IF;
    IF NEW.BuildingID = 3 AND NEW.BFloorNumber > 9 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The Number of Floors is larger than 9';
    END IF;
    IF NEW.BuildingID = 4 AND NEW.BFloorNumber > 9 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The Number of Floors is larger than 9';
    END IF;
    IF NEW.BuildingID = 5 AND NEW.BFloorNumber > 11 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The Number of Floors is larger than 11';
    END IF;
END$$
DELIMITER ;

insert into dims.buildings values
(1, '女1', 8, '赵阿姨', '11111111111'),
(2, '女2', 8, '钱阿姨', '22222222222'),
(3, '女3', 9, '孙阿姨', '33333333333'),
(4, '女4', 9, '李阿姨', '55555555555'),
(5, '女5', 11, '周阿姨', '66666666666');