Vote Up 0 Vote Down

mysql trigger delete multiple tables

What are triggers ? Can I delete data from two or more tables in response to a database event ? I have the following tables :

blog (id,title,description)
blog_tags(id,blogid,tag_name)
blog_comments(id,blogid,user_name,comment)

If I delete a blog entry from the blog table the corresponding entires in blogtags table and blogcomments table should be deleted. How I can make use of triggers in this case ?


Answers


Vote Up 0 Vote Down

Triggers are used to data integrity and avoid data . use triggers for deleting more than two tables also. Before initialing triggers we to change mysql delimiter operator temporarily. Because Triggers use semicolon (;) operator to multiple sql commands.

In your case use the follow commands one by one:

Step 1:

Change delimiter,

delimiter $$

Step 2:

Create trigger,

  CREATE TRIGGER `blog_before_delete`     
  AFTER DELETE ON `blog`     
  FOR EACH ROW     
 BEGIN
  DELETE FROM blog_tags where blogid = OLD.id;
        DELETE FROM blog_comments where blogid = OLD.id;
 END
 $$

Step 3:

Restore delimiter,

delimiter ;

Explanation :

Here the OLD is a keyword and refers to the blog table row that we need to delete. Mysql initiates the trigger blogbeforedelete whenever we delete a entry in the blog table. All the tags related to the blog are deleetd and all the comments related to the blog are deleted. This ensures that no unwanted data exists in the database. Also the process is automatic.

flag | link |

Your Answer

Who Are You?

Have an account already? Login before answering!