问Sqlit外部密钥反向级联删除

EN

sqlite> PRAGMA foreign_keys=on;
sqlite> CREATE TABLE movies(id INTEGER PRIMARY KEY, name);
sqlite> CREATE TABLE songs(id INTEGER PRIMARY KEY, name, movie_id INTEGER REFERENCES movies(id) ON DELETE CASCADE);
sqlite> CREATE INDEX songs_movie_id_idx ON songs(movie_id);
sqlite> CREATE TRIGGER cleanup_movies AFTER DELETE ON songs WHEN ((SELECT count(*) FROM songs WHERE movie_id=old.movie_id) = 0) BEGIN DELETE FROM movies WHERE id = old.movie_id; END;
sqlite> INSERT INTO movie VALUES (1, 'Some Movie');
sqlite> INSERT INTO song VALUES (1, 'Some Song', 1), (2, 'Some Other Song', 1);
sqlite> DELETE FROM songs WHERE id = 1;
sqlite> SELECT * FROM movies;
id          name      
----------  ----------
1           Some Movie
sqlite> DELETE FROM songs WHERE id = 2;
sqlite> SELECT * FROM movies;
sqlite>

CREATE TRIGGER cleanup_movies AFTER DELETE ON songs
WHEN ((SELECT count(*) FROM songs WHERE movie_id=old.movie_id) = 0)
BEGIN
 DELETE FROM movies WHERE id = old.movie_id;
END;