我想要一个触发器从 MySQL 中的 2 个表中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4798768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I want a trigger to DELETE from 2 tables in MySQL
提问by Alexander Shlenchack
I have 3 MySQL tables (food, apple, and orange).
我有3个MySQL表（food，apple，和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?
food(iduser, name)使用一个触发器删除一行时？
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?
但它不会编译。如何制作一次从两个表中删除的触发器？
回答by Jayan Bee
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.
在初始化触发器之前，我们需要临时更改 mysql 分隔符运算符，因为触发器使用分号;运算符来指定触发器内的多个 sql 命令。
Step 1 Change current delimiter:
步骤 1 更改当前分隔符：
delimiter $$
Step 2 Create trigger:
步骤 2 创建触发器：
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:
步骤 3 恢复之前的分隔符：
delimiter ;
Explanation:
解释：
OLDis a builtin keyword and refers to the blog table row that we are deleting. Mysql runs the trigger blog_before_deletewhenever 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 Durabilityin our database.
OLD是一个内置关键字，指的是我们要删除的博客表行。blog_before_delete每当我们删除博客表中的条目时，Mysql 都会运行触发器。我触发器失败，然后删除被回滚。这有助于确保我们数据库中的原子性、一致性、隔离性和持久性。
回答by Randy
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.
删除语句可能需要 OLD.iduser 并且不支持 NEW.iduser。检查您的手册。
回答by Mchl
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.
不需要触发器。
回答by Alexander Shlenchack
I forgot the BEGINand ENDblocks in the trigger. And you have to delete from tables sequentially like this:
我忘记了触发器中的BEGIN和END块。你必须像这样从表中依次删除：
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
回答by Eric Leschinski
MySQL walkthrough of making a trigger to delete two tables with one trigger:
MySQL 制作触发器以使用一个触发器删除两个表的演练：
1. Login:
1.登录：
el@apollo:~$ mysql -u root -p
Enter password:
mysql> use your_database;
Database changed
2. Create some test tables and insert rows:
2. 创建一些测试表并插入行：
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:
3.制作触发器：
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.
4. 查看表是否存在并且所有三个表都已填充。
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
5.从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:
6. 看到derp、foo和bar现在是空的：
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)
