phpMyAdmin 创建一个触发器，用于在不存在子行时删除父行 [英] phpMyAdmin create a trigger for deleting a parent row when no child rows exist

问题描述

在 phpMyAdmin 中，我有两个表:列表和教科书.列表具有教科书的外键 (isbn).我正在尝试创建一个触发器，在列表中的条目被删除后，它会检查列表中是否有任何其他行与被删除的行具有相同的 isbn.如果没有具有此 isbn 的行，则删除教科书中具有该 isbn 的行.这是我在 phpMyAdmin 中尝试过的代码:

In phpMyAdmin I have two tables: listings and textbooks. listings has a foreign key (isbn) from textbooks. I'm trying to create a trigger which, after an entry from listings is deleted, checks to see if there are any other rows in listings with the same isbn as the row that was deleted. If there are no rows with this isbn, then the row with that isbn in textbooks is deleted. This is the code I've tried in phpMyAdmin:

CREATE TRIGGER del_textbook AFTER DELETE ON listings
FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM listings WHERE isbn = (OLD.isbn)) > 0
    DELETE FROM textbooks WHERE isbn = (OLD.isbn);
END IF;
END;

它给出了这个错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL     server version for the right syntax to use near 'DELETE FROM textbooks WHERE isbn = (OLD.isbn); END IF; END' at line 5 

在执行此操作之前，我还将分隔符设置为//.

I also set the delimiter to // before executing this.

谁能告诉我我的代码格式是否正确，或者为什么会发生此错误?我知道 phpmyAdmin 的格式有点挑剔.提前致谢！

Can anybody tell me if my code is formatted correctly, or why this error is occurring? I know that phpmyAdmin is kind of finicky with its formatting. Thanks in advance!

推荐答案

想通了.首先，不要使用SQL查询窗口.使用触发器选项卡.我也忘记了THEN"，所以代码看起来像:

Figured it out. First of all, don't use the SQL query window. Use the Triggers tab. I also forgot the "THEN", so the code looks like:

BEGIN
     IF ((SELECT COUNT(*) FROM listings WHERE isbn = OLD.isbn) < 1) THEN
     DELETE FROM textbooks WHERE isbn = OLD.isbn;
     END IF;
END

这篇关于phpMyAdmin 创建一个触发器，用于在不存在子行时删除父行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！