MySQL触发器:删除后从表中删除 [英] MySQL Trigger: Delete From Table AFTER DELETE

问题描述

范围:两个表.创建新的顾客时，他们会将有关他们的一些信息存储到第二张表中(这也使用触发器完成，它可以按预期工作).这是我的表结构和关系的示例.

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.

表1-> 顾客

+-----+---------+-----+
+  id +   name  + val +
+=====+=========+=====+
+  37 +  george +  x  +
+-----+---------+-----+
+  38 +  sally  +  y  +
+-----+---------+-----+

表2-> patron_info

+----+-----+----------+
+ id + pid +   name   +
+----+-----+----------+
+  1 +  37 +  george  +
+----+-----+----------+
+  2 +  38 +  sally   +
+----+-----+----------+

管理员可以管理顾客.当他们选择移除顾客时，该顾客将从表1 patrons中移除.此时，表2 patron_info没有任何反应.

The administrator can manage the patrons. When they choose to remove a patron, the patron is removed from the table 1 patrons. At this point, nothing happens to table 2 patron_info.

我只是在尝试创建一个触发器，以便在表1中有一个项目被删除时从表2中删除.这就是我尝试过的...

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

最初，我尝试删除触发器(如果存在的话)(只是为了清除空气)...

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

这时，出现语法错误1046: Check syntax near END on line 6.我不知道这是什么错误.我尝试了几种不同的变体.另外，我是否需要在此处使用定界符?

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?

任何人都可以帮助恢复我的理智吗?

Can anyone help restore my sanity?

推荐答案

我认为触发代码中有错误. 要删除带有已删除顾客ID的所有行，必须使用 old.id (否则它将删除其他ID)

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)

尝试将其作为新触发器:

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

不要忘记删除查询中的;" . 另外，如果您要在控制台窗口中输入TRIGGER代码，请也使用定界符.

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.

这篇关于MySQL触发器:删除后从表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！