需要 MySQl 触发器帮助
使用 MySQL 5.1.36，我尝试编写触发器，从“临时”数据库中删除临时表。
CREATE DEFINER=`root`@`localhost` TRIGGER
`jobq`.`DropScratch`
BEFORE DELETE ON jobq.jobq FOR EACH ROW
BEGIN
DECLARE tblname VARCHAR(128);
set tblname=concat('scratch.',OLD.jobname);
DROP TABLE IF EXISTS tblname;
END;
我总是收到一个错误：
Explicit or implicit commit is not allowed in stored function or trigger.
我能以某种方式克服这个限制吗？
预先谢谢你
阿尔曼
using MySQL 5.1.36, I am trying to write trigger which drops scratch tables form "scratch" database.
CREATE DEFINER=`root`@`localhost` TRIGGER
`jobq`.`DropScratch`
BEFORE DELETE ON jobq.jobq FOR EACH ROW
BEGIN
DECLARE tblname VARCHAR(128);
set tblname=concat('scratch.',OLD.jobname);
DROP TABLE IF EXISTS tblname;
END;
I am always getting an error:
Explicit or implicit commit is not allowed in stored function or trigger.
Can I somehow overcome this restriction?
Thank you beforehand
Arman
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这里的主要问题是不允许您删除触发器内的表。这就是错误消息显示不允许“隐式提交”时出现的情况。删除表执行隐式提交。
因此，您需要找出除触发器之外的其他方法来执行此操作。一种方法是设置一个 cron 作业，将 information_schema.tables 中的数据与 jobq 表进行比较，以查找临时数据库中可以删除的表，然后删除它们。
我还应该指出，您尝试动态创建删除表语句的方式将不起作用。这将删除一个字面名称为“tblname”的表，而不是“scratch.jobname”。如果您想动态删除表，则需要使用单独的脚本语言（例如 python、perl、shell 等）构建删除表语句。
祝您好运！
The primary problem here is that you are not allowed to drop a table within a trigger. That's what the error message is getting at when it says "implicit commit" is not allowed. The drop table does an implicit commit.
So you will need to figure out a different way to do this other than a trigger. One way would be to set up a cron job which compares the data in information_schema.tables to the jobq table to look for tables in the scratch DB that can be dropped, and then drop them.
I should also point out that the way you are trying to dynamically create a drop table statement will not work. That is going to drop a table named literally "tblname", not "scratch.jobname". If you want to drop a table dynamically you will need to build the drop table statement in a separate scripting language, such as python, perl, shell, etc.
Good luck!