Thread: DROP Database In Trigger
01-01-14, 11:04 #1Registered User
- Join Date
- Jan 2014
Unanswered: DROP Database In Trigger
I have a program Which Uses Two Databases.
One Database is the main database and controls the system, the other Database is dynamically created to be used whilst the program is running.
when the program finishes it Updates the main Database table to let it know that it has finished with the database.
At this point id like to create a trigger on the main database table to drop the database that i no longer need.
I don't really want to drop it in the program because there could be other clients using the database after the one thats just exited
this is what i have already but obviously this won't work because it uses a prepared statement. I have also tried moving the prepaed statement in a Procedure and calling that, but as i also learnt this is also not allowed
CREATE TRIGGER `Main`.`DeleteDataBase` AFTER UPDATE on `Main`.`Match` FOR EACH ROW BEGIN IF New.Clients = 0 THEN set @sqlstr = CONCAT('Drop Database ',New.DB_NAME); PREPARE stmt1 FROM @sqlstr; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END IF; END$$
01-01-14, 11:23 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
I'm puzzled by the distinction between dropping the database from your program versus dropping it from a trigger or procedure.
Without knowing the architecture of your system (server, app, etc.) I can't offer a well informed opinion, but I'd probably just create a temporary database and leave it on the server or I'd somehow "tag" the temporary database to ensure that only one process uses it so that it could be safely dropped. I'm not very good at coping with non-deterministic code which is what it sounds like you are trying to do.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.