database - SQLite 中的级联触发器

Question

我在 SQLite 中有以下数据库结构:

我想创建一个触发器，每当我删除一个国家时，所有相关的地区、城市和教区也被删除(比如 MySQL InnoDB)，我尝试使用 SQLite 触发器并想出了这个:

地区:

CREATE TRIGGER [delete_country]
BEFORE DELETE
ON [countries]
FOR EACH ROW
BEGIN
DELETE FROM districts WHERE districts.id_countries = id;
END

直辖市:

CREATE TRIGGER [delete_district]
BEFORE DELETE
ON [districts]
FOR EACH ROW
BEGIN
DELETE FROM municipalities WHERE municipalities.id_districts = id;
END

教区:

CREATE TRIGGER [delete_municipality]
BEFORE DELETE
ON [municipalities]
FOR EACH ROW
BEGIN
DELETE FROM parishes WHERE parishes.id_municipalities = id;
END

我还没有测试 delete_district 和 delete_municipality 触发器，因为我在 delete_country 触发器上得到了一个奇怪的行为:当我删除一个country 只有第一个相关的地区被删除，所有其他相关的地区都保留在表中。我做错了什么？

Answer 1

触发器看起来是删除id等于id_countries的区，也就是where子句其实是

WHERE districts.id_countries = districts.id

您需要从国家表中引用 ID。在删除触发器中，使用“旧”来执行此操作。

CREATE TRIGGER [delete_country]
BEFORE DELETE
ON [countries]
FOR EACH ROW
BEGIN
DELETE FROM districts WHERE districts.id_countries = old.id;
END

此外，我建议更改您的架构命名约定。通常，表名是单数的，对应一行中的实体。我会有一个包含列 id 和名称的国家/地区表，一个包含 id、country_id 和名称等的地区表。

country
-------
id
name

district
-------
id
country_id
name

municipality
------------
id
district_id
name

parish
-----
id
municipality_id
name

那么触发器就是

CREATE TRIGGER [delete_country]
BEFORE DELETE
ON [country]
FOR EACH ROW
BEGIN
DELETE FROM district WHERE district.country_id = old.id;
END