Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014

    Unanswered: DROP Database In Trigger

    Hi There
    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`
        IF New.Clients = 0 THEN 
    	set @sqlstr = CONCAT('Drop Database ',New.DB_NAME);
    	PREPARE stmt1 FROM @sqlstr;	
    	EXECUTE stmt1;
        END IF;
    many thank for nay help !!


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts