error: This command is not supported in the prepared statement protocol yet
问题描述:想为一组表创建触发器。实现时,创建了一个临时表,用来存储表名,然后循环,为各个表创建触发器。代码如下:
-- 临时表
CREATE TEMPORARY TABLE TABLIST (
ID INT NOT NULL AUTO_INCREMENT,
V_TABLE VARCHAR(128),
PRIMARY KEY(ID));
-- 创建触发器
DROP PROCEDURE IF EXISTS TEXT_MODLOG_CreateTriggers //
CREATE PROCEDURE TEXT_MODLOG_CreateTriggers()
TEXT_MODLOG_CreateTriggers:BEGIN
DECLARE V_TABLE_P VARCHAR(128);
DECLARE v_num INT;
DECLARE p INT DEFAULT 0;
DECLARE V_TABLEname varchar(32);
INSERT INTO TABLIST(V_TABLE) VALUES ('aaaa');
INSERT INTO TABLIST(V_TABLE) VALUES ('bbbb');
select count(*) into v_num from TABLIST;
tt:LOOP
SET p =p +1;
select V_TABLE into V_TABLE_P from TABLIST where id=p;
set V_TABLEname := CONCAT( V_TABLE_P , '_MODLOG');
-- trigger after insert
SET @V_SQL := CONCAT('drop trigger if exists TRIG_FM_',V_TABLE_P,'_INSERT');
PREPARE stmt FROM @V_SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @V_SQL := CONCAT('create trigger TRIG_FM_' , V_TABLE_P ,'_INSERT', '
after insert on ' , V_TABLE_P , '
for each row
call TEXT_modlog_addorupdate(V_TABLEname, NEW.id, NEW.dbid, 0); ');
PREPARE stmt FROM @V_SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF p < LAST_INSERT_ID() THEN ITERATE tt ; END IF;
LEAVE tt;
END LOOP tt;
end TEXT_MODLOG_CreateTriggers;
//
-- 调用存储过程创建触发器
call TEXT_MODLOG_CreateTriggers();
//
运行结果是:
存储过程创建成功,但是调用时,出现如下的错误:
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
请问下,我哪里出问题了,并且该如何改正,谢谢大家乐。