How to create a trigger in mysql that when deleting a row from a table, I deleted a database?

Question

How to create a trigger in mysql that when deleting a row from a table, I deleted a database?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

0

I have a table in mysql of name Contracts who has the sig. structure:

----------------------
|id|nombre |fecha     |
----------------------
|1 |Emp1   |2015-02-12|
-----------------------
|2 |Emp2   |2016-02-11|
-----------------------

Every time I create a contract, it creates a database with the id of the created contract, example: NombreBD_1 .
What I am looking for is that every time I delete a contract I delete the database that has the id of this, I have researched but I do not understand very well how the triggers work in mysql , I guess it's the best way to do it.

mysql trigger

asked by Alan 14.07.2016 в 00:19

source

3 answers

Understanding META VIEWPORT Subtract or add month to a [duplicate] date

score 1 · Answer 1

Good companion, I am sorry to tell you that in the case you mention, it is not possible to use a trigger because they act on tables and not on BBDD. The ideal would be to use a function or procedure that does act on the database. An example of what you need, could be:

delimiter //

create procedure nombre_procedimiento ( in id_contrato int(50), in nombre_BBDD string (20))

begin 
    DROP DATABASE nombre_BBDD;

end //  
delimiter ;

To call the procedure, where 1 is the id and in single quotes the name of the BBDD

call nombre_procedimiento (1,'nombre_BBDD');

Greetings.

score 1 · Answer 2

It is not possible to do this because the format of the statement DROP can not receive a variable indicating the DB to which it is trying to delete.

A possible solution would be to add a DB of works, in which to insert the DB to be deleted and then with an external script to delete the works that are in this DB. Something like this:

CREATE TRIGGER after_delete_tg BEFORE DELETE ON contratos
FOR EACH ROW 
BEGIN
  INSERT INTO dbs_a_borrar(nombre_db) VALUES(OLD.nombre);
END;

and then a script that takes the values of this DB and generates the necessary SQL to perform the deletion.

greetings

score 1 · Answer 3

Good, first create the triger as indicated, and then what you should do is call a procedure that we will create below:

 DROP procedure IF EXISTS 'borrarBaseDeDatos';

DELIMITER $$
USE 'tubasededatosaqui'$$
CREATE DEFINER='root'@'localhost' PROCEDURE 'borrarBaseDeDatos'(nombreDB varchar(40))
BEGIN

    SET @deleteBase = CONCAT('DROP DATABASE ',@nombreDB);
    PREPARE eliminar FROM @deleteBase;
    EXECUTE eliminar;

    DEALLOCATE PREPARE eliminar;

- you must not forget to do DEALLOCATE. END $$

DELIMITER ;

and the only thing you have to do is send the name of the database you want to delete, in the same way if you need to, you can remove the users' permissions and others.

I hope it's useful for you.