--------------------------------触发器创建新分类触发存储过程-----------------------------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());
报错信息
展开
如果觉得《mysql 1422_mysql触发器调用存储过程 报1422错》对你有帮助，请点赞、收藏，并留下你的观点哦！
