I want a trigger to DELETE from 2 tables in MySQL

问题:

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?

回答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.

回答2:

CREATE TRIGGER `food_before_delete`     
AFTER DELETE ON `food`     
FOR EACH ROW     
begin
  DELETE FROM apple
  WHERE apple.iduser=NEW.iduser;

  DELETE FROM orange
  WHERE orange.iduser=NEW.iduser;
end

The delete statements may require OLD.iduser and not support NEW.iduser. Check your manual.

回答3:

Something simpler maybe?

DELETE f,a,o FROM
food AS f 
LEFT JOIN apple AS a USING (iduser)
LEFT JOIN orange AS o USING (iduser)
WHERE f.name = ...

No trigger needed.

回答4:

I forgot the BEGIN and END blocks in the trigger. And you have to delete from tables sequentially like this:

CREATE TRIGGER `food_before_delete`     
  AFTER DELETE ON `food`     
  FOR EACH ROW     
BEGIN
  DELETE FROM apple
  WHERE apple.iduser=NEW.iduser;

  DELETE FROM orange
  WHERE orange.iduser=NEW.iduser; 
END

回答5:

MySQL walkthrough of making a trigger to delete two tables with one trigger:

1. Login:

el@apollo:~$ mysql -u root -p
Enter password: 
mysql> use your_database;
Database changed

2. Create some test tables and insert rows:

mysql> create table derp(derp1 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> create table foo(foo1 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> create table bar(bar1 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into derp values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into foo values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bar values (1);
Query OK, 1 row affected (0.00 sec)

3. Make the trigger:

mysql> delimiter //
mysql> create trigger delete_foo_and_bar_when_derp_is_deleted
    -> after delete on derp
    -> for each row
    -> begin
    -> delete from foo where foo1=OLD.derp1;
    -> delete from bar where bar1=OLD.derp1;
    -> end//
Query OK, 0 rows affected (0.02 sec)

4. See the tables exist and all three are populated.

mysql> delimiter ;
mysql> select * from derp;
+-------+
| derp1 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> select * from foo;
+------+
| foo1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from bar;
+------+
| bar1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

5. Delete from derp

mysql> delete from derp where derp1 = 1;
Query OK, 1 row affected (0.01 sec)

6. See that derp, foo, and bar are empty now:

mysql> select * from derp;
Empty set (0.00 sec)

mysql> select * from foo;
Empty set (0.00 sec)

mysql> select * from bar;
Empty set (0.00 sec)