mysql 1422_mysql触发器调用存储过程，报1422错

--------------------------------触发器创建新分类触发存储过程-----------------------------DROPTRIGGERIFEXISTScategory_insert_trigger///CREATETRIGGERcategory_insert_trigger...

-- ----------------------------

-- 触发器 创建新分类触发存储过程

-- ---------------------------

DROP TRIGGER IF EXISTS category_insert_trigger ///

CREATE TRIGGER category_insert_trigger AFTER INSERT ON tb_template_category FOR EACH ROW

BEGIN

CALL show_category_tree_procedure(0);

END;

///

-- ----------------------------

-- 存储过程 显示分类的树形结构

-- ----------------------------

DROP PROCEDURE IF EXISTS show_category_tree_procedure ///

CREATE PROCEDURE show_category_tree_procedure (IN rootid INT)

BEGIN

DECLARE category_level int ;

DROP TABLE IF EXISTS tmp_category_table;

CREATE TABLE tmp_category_table (

tmp_id int,

tmp_level int,

tmp_scort varchar(8000)

);

Set category_level=0 ;

INSERT into tmp_category_table SELECT id, category_level, id FROM tb_template_category WHERE parent_id = rootid;

WHILE ROW_COUNT()>0 DO

SET category_level=category_level+1 ;

INSERT into tmp_category_table

SELECT A.id, category_level, concat(B.tmp_scort,A.id)

FROM tb_template_category A,tmp_category_table B

WHERE A.parent_id = B.tmp_id AND B.tmp_level=(category_level-1);

END WHILE;

END;

///

insert语句如下：

INSERT INTO `tb_template_category` VALUES ('7', '3', '测试分类7', '1', '1', '测试分类描述7', current_timestamp(), current_timestamp());

报错信息

展开