这个触发器错在哪里?
触发器代码如下:
CREATE TRIGGER leases_insert
after insert on dhcp_leases for each row
begin
declare nettype enum;
declare cmts_ip varchar(16);
declare cmts_vlan smallint;
declare network varchar(20);
declare range_min int;
declare range_max int;
declare new_ip int;
declare flag int;
declare cur1 CURSOR FOR select cmts_ip,nettype,cmts_vlan,network,INET_ATON(range_min),INET_ATON(range_max) from config_nets;
declare continue handler for not found set flag=1;
select INET_ATON(new.ipaddr) into new_ip;
OPEN cur1;
repeat
FETCH cur1 INTO cmts_ip,nettype,cmts_vlan,network,range_min,range_max;
if new_ip>=range_min && new_ip<=range_max then
update dhcp_stat set ip_used = ip_used+1,used_point=ip_used*100/ip_total
where dhcp_stat.cmts_ip=cmts_ip and dhcp_stat.cmts_vlan=cmts_vlan
and dhcp_stat.nettype=nettype and dhcp_stat.network=network;
end if;
until flag = 1
end repeat;
close cur1;
end;
完全没有效果,数据库版本是5.1.23-rc
引用触发器能够工作,但是好像多循环了一次:
