MySQL游标多一次循环

触发器代码片段如下:

declare c_name cursor for select b.id from asset_db_priv a,mc$priv_database b where a.node_name=b.priv_name and a.p_node_name=new.name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
	set pnid=new.id;
	open c_name;
	REPEAT
        fetch c_name into p_id;
	    insert into db_operation_priv(op_id,priv_db_id,status) values (pnid,p_id,'1');
	UNTIL done END REPEAT;
	close c_name;

发现db_operation_priv表格会被多插一条记录(最后一条重复)。查看触发器代码很明显done = 1时还会执行一次insert,那么在insert之前加一层判断即可。我先加了if not done then或者if done<>1 then,发现insert 一次都没有执行,将done值插入测试表发现done在变为1之前都是null,改为if done is null then即可。
改好的代码片段如下:

declare c_name cursor for select b.id from asset_db_priv a,mc$priv_database b where a.node_name=b.priv_name and a.p_node_name=new.name;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
	set pnid=new.id;
	open c_name;
	REPEAT
        fetch c_name into p_id;
        if done is null then
	    insert into db_operation_priv(op_id,priv_db_id,status) values (pnid,p_id,'1');
	end if;
	UNTIL done END REPEAT;
	close c_name;