mysql存储过程动态创建触发器

[zhangxu724100]

mysql存储过程中如何给很多表动态创建触发器？我测试了下：
CREATE DEFINER=`root`@`localhost` PROCEDURE `creattrigger`()
BEGIN
set @sql="CREATE TRIGGER updateValueDay AFTER UPDATE ON value20001 FOR EACH ROW
BEGIN
insert into valueday(time1) values('2111-1-1');
END;";
prepare stmt1 from @sql;
EXECUTE stmt1;


END


报错：1259：——this command is not supported in the prepared statement protocol yet

[zhangxu724100]

高手帮帮忙，谢谢

[ACMAIN_CHM]

MYSQL 5.4 中也仅在PREPARE中支持以下语句。

ALTER TABLE
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} DATABASE
{CREATE | RENAME | DROP} USER
CREATE INDEX
CREATE TABLE
DELETE
DO
DROP INDEX
DROP TABLE
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
| LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
UNINSTALL PLUGIN
UPDATE

[zhangxu724100]

那怎么在存储过程中创建触发器？

[ACMAIN_CHM]

[Quote]那怎么在存储过程中创建触发器？[/Quote]

直接CREATE TRIGGER TBLNAME 就行。 只不过是不能在PREPARE中使用CREATE　TRGGIER，在存储过程还是可以的。

[zhangxu724100]

怎么创建？我这样建的：
BEGIN
CREATE TRIGGER updateValueDay AFTER UPDATE ON value20001 FOR EACH ROW
BEGIN
insert into valueday(time1) values('2111-1-1');
END;
END

报错：can't create a trigger from within another stored routine

[zhangxu724100]

[Quote=引用 3 楼 zhangxu724100 的回复:]
那怎么在存储过程中创建触发器？
[/Quote]

怎么创建？我这样建的：
BEGIN
CREATE TRIGGER updateValueDay AFTER UPDATE ON value20001 FOR EACH ROW
BEGIN
insert into valueday(time1) values('2111-1-1');
END;
END

报错：can't create a trigger from within another stored routine

[zuoxingyu]

Category Timestamp Duration Message Line Position
Error 2010-11-22 10:13:29 0:00:00.015 MySQL Database Error: Can't create a TRIGGER from within another stored routine 8 0


我测试也是这个错误。
版本是5.1.45的.

[zhangxu724100]

看来触发器是没戏了，直接在程序里面改用存储过程手动更新了