环境情况：
表1：residential_building,住宅楼表:id,community(所属社区），countFloor（楼层数），countUnit（单元数），countHomesInUnit（每单元户数），buildingName（楼栋名称）
表2：homeNumber，住户表 id，buildingID(表1的id），homeNumber
根据表1楼栋信息，自动填充表2中每个房间号的内容
创建表1：
drop table if exists residential_building;
create table residential_building(
id int not null auto_increment primary key,
community varchar(20),
countFloor tinyint,
countUnit tinyint,
countHomesInUnit tinyint,
buildingName varchar(50)
)character set utf8;
创建表2：
drop table if exists building_home;
create table building_home(
id int not null auto_increment primary key,
buildingID int,
homeNumber varchar(50)
)character set utf8;
insert触发器，表一插入内容时，表2根据楼层单元信息生成房间号
DROP TRIGGER IF EXISTS createHomeNumber;
delimiter $$
create trigger createHomeName after insert on residential_building
for each row
begin
declare floor int default 1;
declare num int default 1;
declare roomNumber varchar(50) CHARACTER SET utf8 default "";
while floor <= new.countFloor do
while num <= new.countUnit * new.countHomesInUnit do
if num<10 then
set roomNumber = concat(new.buildingName,floor,"0",num);
else
set roomNumber = concat(new.buildingName,floor,num);
end if; insert into building_home (buildingID,homeNumber) values(new.id,roomNumber);
set num = num + 1;
end while;
set floor = floor + 1;
set num = 1;
end while;
end
$$
delimiter ;
del触发器，当表一中楼栋被删除时，表2相应记录被删除
DROP TRIGGER IF EXISTS delHomeNumber;
delimiter $$
create trigger delHomeNumber after delete on residential_building
for each row
begin
delete from building_home where buildingID = old.id;
end
$$
delimiter ;
update 触发器，当表1中楼栋信息被修改时，表2重新创建
DROP TRIGGER IF EXISTS updateRoomNumber;
delimiter $$
create trigger updateRoomNumber after update on residential_building
for each row
begin declare floor int default 1;
declare num int default 1;
declare roomNumber varchar(50) CHARACTER SET utf8 default "";
delete from building_home where buildingID = old.id;
while floor <= new.countFloor do
while num <= new.countUnit * new.countHomesInUnit do
if num<10 then
set roomNumber = concat(new.buildingName,floor,"0",num);
else
set roomNumber = concat(new.buildingName,floor,num);
end if; insert into building_home (buildingID,homeNumber) values(old.id,roomNumber);
set num = num + 1;
end while;
set floor = floor + 1;
set num = 1;
end while;
end
$$
delimiter ;