测试数据
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(`qty` INT(1));DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`(`qty` INT(1));DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3`(
`type` TINYINT(1) DEFAULT '0',
`qty` INT(1)
);DELIMITER $$
DROP PROCEDURE IF EXISTS `p_test`$$
CREATE PROCEDURE `p_test`(tab VARCHAR(40), qty INT(1))
BEGIN
SET @ts =CONCAT('INSERT INTO `', tab, '`(`qty`) VALUES(', qty, ')');
PREPARE s FROM @ts;
EXECUTE s;
DROP PREPARE s;
END$$DROP TRIGGER IF EXISTS `insert_t3`$$
CREATE TRIGGER `insert_t3` BEFORE INSERT ON `t3`
FOR EACH ROW
BEGIN
DECLARE tab VARCHAR(40) DEFAULT 't1';
IF NEW.`type` THEN SET tab='t2';
END IF;
CALL `p_test`(tab, NEW.qty);
END$$
DELIMITER ;
执行
INSERT INTO `t3` VALUES(1,2);
时,提示
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(`qty` INT(1));DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2`(`qty` INT(1));DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3`(
`type` TINYINT(1) DEFAULT '0',
`qty` INT(1)
);DELIMITER $$
DROP PROCEDURE IF EXISTS `p_test`$$
CREATE PROCEDURE `p_test`(tab VARCHAR(40), qty INT(1))
BEGIN
SET @ts =CONCAT('INSERT INTO `', tab, '`(`qty`) VALUES(', qty, ')');
PREPARE s FROM @ts;
EXECUTE s;
DROP PREPARE s;
END$$DROP TRIGGER IF EXISTS `insert_t3`$$
CREATE TRIGGER `insert_t3` BEFORE INSERT ON `t3`
FOR EACH ROW
BEGIN
DECLARE tab VARCHAR(40) DEFAULT 't1';
IF NEW.`type` THEN SET tab='t2';
END IF;
CALL `p_test`(tab, NEW.qty);
END$$
DELIMITER ;
执行
INSERT INTO `t3` VALUES(1,2);
时,提示
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
还有不能用SELECT * FROM tbl_name这种查询语句。
CALL `p_test`(tab, NEW.qty);
END$$你在这调用了