mysql触发器的使用

环境情况:

表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 ;
上一篇:数据结构(java)


下一篇:GNU 项目(开源社区的由来,背后的哲学)