这个触发器错在哪里?

Question

触发器代码如下:
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 

Answer 1

提供你相关的这几个表的 create table 语句，　这样别人可以直接在机器上进行测试。

Answer 2

建表语句如下:
CREATE TABLE `config_nets` (
  `nettype` enum('CM','CPE') NOT NULL DEFAULT 'CPE',
  `cmts_ip` varchar(16) NOT NULL DEFAULT '',
  `cmts_vlan` smallint(5) unsigned NOT NULL DEFAULT '0',
  `network` varchar(20) NOT NULL DEFAULT '',
  `gateway` varchar(16) NOT NULL DEFAULT '',
  `dns` varchar(16) NOT NULL,
  `dns2` varchar(16) NOT NULL,
  `grant_flag` enum('YES','NO') NOT NULL DEFAULT 'YES',
  `dynamic_flag` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `full_flag` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `range_min` varchar(16) NOT NULL DEFAULT '',
  `range_max` varchar(16) NOT NULL DEFAULT '',
  `lease_time` int(10) unsigned NOT NULL DEFAULT '0',
  `config_opt1` smallint(5) unsigned NOT NULL DEFAULT '0',
  `config_opt2` smallint(5) unsigned NOT NULL DEFAULT '0',
  `config_opt3` smallint(5) unsigned NOT NULL DEFAULT '0',
  `domain` varchar(5) DEFAULT NULL COMMENT '??°?????o???(??????é???￠????)',
  PRIMARY KEY (`nettype`,`cmts_ip`,`cmts_vlan`,`network`),
  KEY `cmts_ip` (`cmts_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1CREATE TABLE `dhcp_leases` (
  `ipaddr` varchar(16) NOT NULL DEFAULT '',
  `macaddr` varchar(15) NOT NULL DEFAULT '',
  `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dynamic_flag` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `lockip_flag` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `pc_name` varchar(50) NOT NULL DEFAULT '',
  `subnum` bigint(20) unsigned NOT NULL DEFAULT '0',
  `modem_macaddr` varchar(15) CHARACTER SET gbk NOT NULL DEFAULT '',
  `cmts_vlan` smallint(5) unsigned NOT NULL DEFAULT '0',
  `config_file` varchar(50) NOT NULL DEFAULT '',
  `config_opt` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ipaddr`),
  UNIQUE KEY `ipaddr` (`ipaddr`),
  KEY `macaddr` (`macaddr`),
  KEY `start_time` (`start_time`),
  KEY `end_time` (`end_time`),
  KEY `update_time` (`update_time`),
  KEY `subnum` (`subnum`),
  KEY `modem_macaddr` (`modem_macaddr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `dhcp_stat` (
  `cmts_ip` varchar(16) NOT NULL DEFAULT '',
  `nettype` enum('CPE','CM') NOT NULL DEFAULT 'CPE',
  `cmts_vlan` smallint(5) NOT NULL DEFAULT '0',
  `network` varchar(20) NOT NULL DEFAULT '',
  `used_point` varchar(8) CHARACTER SET latin1 NOT NULL,
  `domain` varchar(5) NOT NULL DEFAULT '',
  `ip_used` decimal(6,0) NOT NULL,
  `ip_total` decimal(6,0) NOT NULL,
  PRIMARY KEY (`cmts_ip`,`nettype`,`cmts_vlan`,`network`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk

Answer 3

建议提供测试用例数据，你的 insert 语句，然后执行完后的结果应该是什么？

Answer 4

表config_nets数据:
INSERT INTO `config_nets` VALUES ('CPE', '180.188.28.1', 1, '180.188.28.0/24', '180.188.28.1', '218.207.128.4', '211.147.6.4', 'YES', 'YES', 'NO', '180.188.28.2', '180.188.28.254', 14400, 1, 1, 1, '508');
INSERT INTO `config_nets` VALUES ('CPE', '180.188.17.1', 1, '180.188.17.0/24', '180.188.17.1', '218.207.128.4', '211.147.6.4', 'YES', 'YES', 'NO', '180.188.17.2', '180.188.17.254', 14400, 1, 1, 1, '502');
表dhcp_stat数据:
INSERT INTO 'dhcp_stat' VALUES('180.188.28.1','CPE',1,'180.188.28.0/24','','508',0,253);
INSERT INTO 'dhcp_stat' VALUES('180.188.17.1','CPE',1,'180.188.17.0/24','','502',0,253);
在dhcp_leases表中插入一条数据如:
INSERT INTO `dhcp_leases` VALUES ('180.188.28.10', '0023cd81881f', '2010-1-7 06:36:23', '2010-1-7 10:36:23', '2010-1-7 06:36:23', 'NO', 'NO', 'NatRouter', 0, '001fc695545d', 1, '', 0);
触发器判断其在cmts_ip为180.188.28.1的ip段内(>=range_min;<=range_max),故在表dhcp_stat中cmts_ip为该值的记录中ip_used+1,used_point = ip_used*100/ip_total

Answer 5

本帖最后由 ACMAIN_CHM 于 2010-04-21 17:55:36 编辑

  

Answer 6

你的触发器创建成功了吗？mysql> delimiter //
mysql> 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_AT
ON(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,rang
e_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=cm
ts_vlan
    ->                 and  dhcp_stat.nettype=nettype and dhcp_stat.network=netw
ork;
    ->                end if;
    ->        until flag = 1
    ->        end repeat;
    ->        close cur1;
    -> end;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ';
        declare cmts_ip varchar(16);
        declare cmts_vlan smallint;
        declare network va' at line 4
mysql> delimiter ;

Answer 7

sorry,触发器版本错了:
CREATE TRIGGER `leases_insert` AFTER INSERT ON `dhcp_leases`
  FOR EACH ROW
begin
declare nettype enum('CM','CPE');
declare cmts_ip varchar(16);
declare cmts_vlan smallint(5);
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;
     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;
    until flag = 1
    end repeat;
    close cur1;
end;

Answer 8

触发器运行出错，而不是不工作。你的字符集不一样啊。mysql> delimiter //
mysql> CREATE TRIGGER `leases_insert` AFTER INSERT ON `dhcp_leases`
    ->  FOR EACH ROW
    -> begin
    ->  declare nettype enum('CM','CPE');
    ->  declare cmts_ip varchar(16);
    ->  declare cmts_vlan smallint(5);
    ->  declare network varchar(20);
    ->  declare range_min int;
    ->  declare range_max int;
    ->    declare new_ip int UNSIGNED;   -- UNSIGNED changed by ACMAIN
    ->  declare flag int;
    ->  declare cur1 CURSOR FOR select cmts_ip,nettype,cmts_vlan,network,INET_AT
ON(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,rang
e_max;
    ->               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_v
lan
    ->                  and dhcp_stat.nettype=nettype and dhcp_stat.network=netw
ork;
    ->    until flag = 1
    ->    end repeat;
    ->    close cur1;
    -> end;
    ->
    -> //
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql>
mysql> delete from dhcp_leases;
Query OK, 1 row affected (0.02 sec)mysql> INSERT INTO `dhcp_leases` VALUES ('180.188.28.10', '0023cd81881f', '2010-
1-7 06:36:23', '2010-1-7 10:36:23', '2010-1-7 06:36:23', 'NO', 'NO', 'NatRouter'
, 0, '001fc695545d', 1, '', 0);
ERROR 1267 (HY000): Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (lat
in1_swedish_ci,IMPLICIT) for operation '='

Answer 9

触发器修改如下:CREATE TRIGGER `leases_insert` AFTER INSERT ON `dhcp_leases`
  FOR EACH ROW
begin
declare nettype enum('CM','CPE');
declare cmts_ip varchar(16);
declare cmts_vlan smallint(5);
declare network varchar(20);
declare range_min int UNSIGNED;
declare range_max int UNSIGNED;
    declare new_ip int UNSIGNED;
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;我这没有报字符集的问题,你试试改一下dhcp_stat的定义帮我再测一下,多谢了:CREATE TABLE `dhcp_stat` (
  `cmts_ip` varchar(16) NOT NULL DEFAULT '',
  `nettype` enum('CPE','CM') NOT NULL DEFAULT 'CPE',
  `cmts_vlan` smallint(5) NOT NULL DEFAULT '0',
  `network` varchar(20) NOT NULL DEFAULT '',
  `used_point` varchar(8) NOT NULL,
  `domain` varchar(5) CHARACTER SET gbk NOT NULL DEFAULT '',
  `ip_used` decimal(6,0) NOT NULL,
  `ip_total` decimal(6,0) NOT NULL,
  PRIMARY KEY (`cmts_ip`,`nettype`,`cmts_vlan`,`network`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 10

触发器能够工作,但是好像多循环了一次:CREATE DEFINER = 'docsis'@'%' TRIGGER `leases_insert` AFTER INSERT ON `dhcp_leases`
  FOR EACH ROW
begin
declare _nettype enum('CM','CPE');
declare _cmts_ip varchar(16);
declare _cmts_vlan smallint(5);
declare _network varchar(20);
declare _range_min int UNSIGNED;
declare _range_max int UNSIGNED;
    declare new_ip int UNSIGNED;
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;

Answer 11

repeat 会多循环一次。建议使用 while