I want a trigger to DELETE from 2 tables in MySQL

Question

I have 3 MySQL tables (food, apple, and orange).

I want to delete rows from:

apple(idapple, iduser, name) 
orange(idornge, iduser, name)

When deleting a row in food(iduser, name) using one trigger?

Here is my trigger so far:

  CREATE TRIGGER `food_before_delete`

    AFTER DELETE ON `food` 
    FOR EACH ROW 

      DELETE FROM apple, orange 
      WHERE 
      apple.iduser=OLD.iduser and 
      orange.iduser=OLD.iduser

But it won't compile. How can make a trigger that deletes from two tables at once?

Answer 1

Delete from two tables at once with a Trigger:

Triggers are used to enforce data integrity in the tables. You can use triggers to delete from any number of tables at once.

Before initializing triggers we need to change the mysql delimiter operator temporarily because triggers use semicolon ; operator to specify multiple sql commands within the trigger.

Step 1 Change current 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 previous delimiter:

delimiter ;

Explanation:

OLD is a builtin keyword and refers to the blog table row that we are deleting. Mysql runs the trigger blog_before_delete whenever we delete an entry in the blog table. I the trigger fails, then the delete is rolled back. This helps ensure Atomicity, Consistency, Isolation, and Durability in our database.