MySQL MySQL触发器：删除后从表中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11818191/
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
MySQL Trigger: Delete From Table AFTER DELETE
提问by Ohgodwhy
Scope: Two tables. When a new patron is created, they have some information about them stored into a 2nd table (This was done using a trigger as well, it works as expected). Here's an example of my table structure and relationship.
范围：两张表。当一个新的顾客被创建时，他们有一些关于他们的信息存储在第二个表中（这也是使用触发器完成的，它按预期工作）。这是我的表结构和关系的示例。
Table 1-> patrons
表1->顾客
+-----+---------+-----+
+ id + name + val +
+=====+=========+=====+
+ 37 + george + x +
+-----+---------+-----+
+ 38 + sally + y +
+-----+---------+-----+
Table 2 -> patron_info
表 2 ->赞助人信息
+----+-----+----------+
+ id + pid + name +
+----+-----+----------+
+ 1 + 37 + george +
+----+-----+----------+
+ 2 + 38 + sally +
+----+-----+----------+
The administrator can manage the patrons. When they choose to remove a patron, the patron is removed from the table 1patrons. At this point, nothing happens to table 2patron_info.
管理员可以管理读者。当他们选择移除顾客时，顾客会从表 1 中移除patrons。此时，表 2没有任何反应patron_info。
I'm simply trying to create a trigger to delete from table 2, when table 1 has an item deleted. Here's what I've tried...
我只是想创建一个触发器来从表 2 中删除，当表 1 删除了一个项目时。这是我尝试过的...
Initially, I try to drop the trigger if it exists (just to clear the air)...
最初，我尝试放下触发器（如果它存在）（只是为了清除空气）......
DROP TRIGGER IF EXISTS log_patron_delete;
Then I try to create the trigger afterwards...
然后我尝试在之后创建触发器......
CREATE TRIGGER log_patron_delete AFTER DELETE on patrons
FOR EACH ROW
BEGIN
DELETE FROM patron_info
WHERE patron_info.pid = patrons.id
END
At this point, I get a syntax error 1046: Check syntax near END on line 6. I don't know what the error is at this point. I've tried several different variations. Also, am I required to use a delimiter here?
此时，我收到了一个语法错误1046: Check syntax near END on line 6。我不知道此时的错误是什么。我尝试了几种不同的变体。另外，我需要在这里使用分隔符吗？
Can anyone help restore my sanity?
谁能帮助我恢复理智？
回答by vivek_jonam
I think there is an error in the trigger code. As you want to delete all rows with the deleted patron ID, you have to use old.id(Otherwise it would delete other IDs)
我认为触发代码中有错误。由于您想删除所有带有已删除顾客 ID 的行，您必须使用old.id（否则它会删除其他 ID）
Try this as the new trigger:
试试这个作为新的触发器：
CREATE TRIGGER log_patron_delete AFTER DELETE on patrons
FOR EACH ROW
BEGIN
DELETE FROM patron_info
WHERE patron_info.pid = old.id;
END
Dont forget the ";"on the delete query. Also if you are entering the TRIGGER code in the console window, make use of the delimiters also.
不要忘记“;” 关于删除查询。此外，如果您在控制台窗口中输入 TRIGGER 代码，也请使用分隔符。
回答by Andrew G.
Why not set ON CASCADE DELETE on Foreign Key patron_info.pid?
为什么不patron_info在外键.pid上设置 ON CASCADE DELETE ？
回答by alireza abedini
create trigger doct_trigger
after delete on doctor
for each row
delete from patient where patient.PrimaryDoctor_SSN=doctor.SSN ;
