mysql触发器检验有效性_mysql 触发器检测表数据添加，进而调用存储过程检测数据，进而调用存储过程添加数据...

触发器：

begin

DECLARE user_mes INT(64);

SELECT user_id into user_mes FROM order_orderlist where id = new.order_orderlist_id limit 1;

Call ListenOrder(user_mes,new.goods_good_id);

end

数据监测存储过程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `ListenOrder`(IN `user_id` int,IN `goods_good_id` int)

BEGIN

DECLARE _word INT;

DECLARE tmpid INT;

DECLARE course_id INT;

DECLARE goods_mes varchar(64);

DECLARE done INT DEFAULT FALSE;

DECLARE cur_course CURSOR FOR select id,good_ids from col_course where good_ids <> '';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_course;

read_loop: LOOP

FETCH NEXT from cur_course INTO course_id,goods_mes;

IF done THEN

LEAVE read_loop;

END IF;

SET _word = LOCATE(',',goods_mes);

WHILE _word > 0

DO

SET tmpid = SUBSTR(goods_mes,1,_word-1);

IF tmpid = goods_good_id THEN

CALL AddUserCourse(user_id,course_id);

END if;

SET goods_mes = SUBSTR(goods_mes FROM _word+1);

SET _word = LOCATE(',',goods_mes);

END WHILE;

IF goods_mes = goods_good_id THEN

CALL AddUserCourse(user_id,course_id);

END if;

END LOOP;

CLOSE cur_course;

END

数据添加存储过程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddUserCourse`(IN `user_id` int,IN `course_id` int)

BEGIN

INSERT into col_usercourse (user_id,course_id,createtime,`status`) VALUES(user_id,course_id,unix_timestamp(now()),'1');

END