MySQl Trigger help needed

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 res开发者_如何学编程triction?

Thank you beforehand

Arman

---

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!