Cascade Triggers in SQLite

Question

I've the following DB structure in SQLite:

I want to create a trigger that whenever I delete a country all the related districts, municipalities and parishes are also deleted (like MySQL InnoDB), I've tried using SQLite triggers and came up with this:

Districts:

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

Municipalities:

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

Parishes:

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

I haven't yet tested the delete_district and delete_municipality triggers because I get a strange behavior on the delete_country trigger: when I delete a country only the first related district gets deleted, all the others related districts remain in the table. What am I doing wrong?

Answer 1

The trigger looks like it is deleting districts whose id equals id_countries, that is, the where clause is actually

WHERE districts.id_countries = districts.id

You need to reference the id from the countries table. In a delete trigger, use "old" to do this.

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

Also, I would suggest changing your schema naming convention. Usually, the table name is singular, and corresponds to the entity in a row. I would have a country table with columns id and name, a district table with id, country_id and name, etc.

country
-------
id
name

district
-------
id
country_id
name

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

parish
-----
id
municipality_id
name

Then the trigger would be

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