I have two tables (Track and Album) and want to prevent the deletion of some tracks that their album Title is 'Lost' with this trigger, but the code applies for all track ids I think. I can not delete any track(track id is the primary key of the track table and albumid is the foreign key of the Album table in Track)
What's wrong in my code? Please help!
Thank you
CREATE TRIGGER Undeletelosttracks AFTER DELETE ON Track
BEGIN
SELECT CASE
WHEN OLD.TrackID = (SELECT Track.TrackID FROM
Track JOIN Album USING(AlbumID)
WHERE Album.Title LIKE 'Lost%'
)
THEN
RAISE(ABORT,'Can not delete')
END;
END;
I'm surprised that you say that your trigger does not allow any deletion from your table.
From what I've tried it does not prevent any deletion.
Use this trigger:
CREATE TRIGGER Undeletelosttracks BEFORE DELETE ON Track
FOR EACH ROW
WHEN EXISTS (
SELECT 1
FROM Album a
WHERE a.AlbumID = OLD.AlbumID AND a.Title LIKE 'Lost%'
)
BEGIN
SELECT RAISE(ABORT,'Can not delete');
END;