Mysql基于编号设计的触发器

在做课程设计的时候,想到用编号来实现对交易信息的管理,所以在做数据库设计的时候也做了相应的设计,但在查询和定义触发器的时候也遇到了相应的困难,特别在此记录一下。

现在我有三张表,分别是交易信息表、序列信息表和房屋信息表,这三个表的结构以及存储的数据分别如下:

1、交易信息(trade_info)表,ID编号的结构为:辖区编号+房产编号+户编号

属性:(交易编号编号,房产经纪人,客户姓名,交易形式,租房时限)

2、序列信息(serial_number)表,数据库中的城市为杭州,数据是从链家找来的数据,编号是人为编的

属性:(辖区编号,辖区名,住房在辖区内的编号,住房名)

3、房屋信息(hosue_info)表,数据从链家中获取

属性:(住房名,辖区,在售房源,在租房源,开发商,户型,售卖价格/平方米,租房价格/月)

我想实现的是定义一个触发器,当交易信息表新增交易时按照租房或者买房,房屋信息表中相应住房的在售房源或者在租房源减一。

因此需要查询交易信息表中的ID,根据ID的前两个值在序列信息表匹配住房名,这部分的SQL语句为

select  Property_NAME from serial_number S where
S.District_SM=left(new.ID,2) and S.Property_SM=substring(new.ID,4,2)

这里的new.ID代表新插入行的ID值,返回的是对应行attribute值从左往右的k个字符,在SQL语句中恰好对应的是序列信息表中的辖区编号,因此选择的Property_NAME所在的行要满足辖区编号一致,即

serial_number.District_SM=left(new.ID,2)

前面的SQL给serial_number取了个别名S,where后面的S就指代serial_number表

同样是取子字符串的操作,所属字段值从start开始取scale位,Mysql定位从1开始而不是0

S.Property_SM=substring(new.ID,4,2)

上述SQL定位了房屋编号,将两者混合能唯一确定房屋名。

定义触发器来实现要实现的功能的SQL语句则是

delimiter //
create trigger trigl after insert on trade_info 
for each row 
begin
	if (new.TradeForm='租房')
		then
		update house_info
		set Num_onRent=Num_onRent-1
		where house_info.HouseName 
		= (select Property_NAME from serial_number S where S.District_SM=left(new.ID,2) and S.Property_SM=substring(new.ID,4,2));
    elseif (new.TradeForm='买房')
		then
		update house_info
		set Num_onSale=Num_onSale-1
		where house_info.HouseName 
		= (select Property_NAME from serial_number S where S.District_SM=left(new.ID,2) and S.Property_SM=substring(new.ID,4,2));
	end if;
end;//

修改delimiter将MySQL的定界符改为//,以防在SQL语句执行的过程中逐句执行,破坏了代码块的整体性。

在begin后面加上了判断来确定交易行为的性质是买房还是租房,从而对house_info内的在售房源或在租房源进行减一操作。

我在刚开始有referencing  new row new语句来声明新插入的行为一个独立单位,网上很多资料还有最初在工大的课上都是这种写法,不知道是Mysql版本更新的原因还是我的写法错误,SQL语句并不能执行,事实上,在begin和end内的new不用声名直接替代了新插入的行能直接访问相应的属性值。

触发器生效的效果如下:

在trade_info里插入一段数据时,trigl触发器会首先确定新插入的字段TradeForm是’买房’还是’租房’,根据交易性质的不同来确定修改的是house_info表中在售房源的数量还是在租房源的数量。

触发器会首先根据交易ID的前两位去serial_number表中找到对应的房产名称

检索到房产名称为’运河印’后再到house_info表中修改字段

由于在租房源新增一条交易信息并且交易形式为’租房’,房产名称为’运河印’的行的’在租房源’字段中的数字自动减去一。