- mysql> DELIMITER //
- mysql> CREATE PROCEDURE ps(IN table_name VARCHAR(200))
- -> BEGIN
- -> SET @newname = table_name;
- -> SET @s = CONCAT('create table ',@newname,'(resourceid varchar(200),',@newname,' varchar(200),time date)');
- -> PREPARE stmt FROM @s;
- -> EXECUTE stmt;
- -> DEALLOCATE PREPARE stmt;
- -> END
- -> //
- Query OK, 0 rows affected (0.00 sec)
- mysql> CREATE TABLE zs_resourcegroup(group_name VARCHAR(20));
- -> ;
- -> //
- Query OK, 0 rows affected (0.01 sec)
- mysql> DELIMITER //
- mysql> CREATE TRIGGER ts AFTER INSERT ON zs_resourcegroup
- -> FOR EACH ROW
- -> BEGIN
- -> CALL ps(new.group_name);
- -> END
- -> //
- Query OK, 0 rows affected (0.00 sec)
- mysql> insert into zs_resourcegroup select 'dba';
- -> //
- <span style="color:#ff0000;"><strong>ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger</strong>
- </span>mysql>
- mysql>
- mysql> call ps("cui");
- -> //
- Query OK, 0 rows affected (0.01 sec)
- mysql> show tables;
- -> //
- +------------------+
- | Tables_in_test |
- +------------------+
- | cui |
- | t |
- | zs_resourcegroup |
- +------------------+
- 3 rows in set (0.00 sec)
上网查了一下,发现触发器中不支持动态sql,单独执行存储过程还是可以支持的。