The SQLITE trigger instructions are not very clear on how to use the WHEN clause to make the trigger conditional. In the current situation there are two triggers, one take the updated row and insert it into another table when the column 'Selected' is 1. Another trigger that will delete the row from the other table when the column 'Selected' is 0.
How exactly do I write the trigger's WHEN clause? Do I need to put it into some type of select statement or can I simply create a Boolean expression?
Here is what I have come up with:
Insert:
CREATE TRIGGER IF NOT EXISTS tu_Favorite_add_to_SlideShowImage AFTER UPDATE
ON Favorite FOR EACH ROW WHEN (NEW.Selected = 1)
BEGIN
INSERT INTO SlideShowImage (FolderId, ImageId, timeMarker)
SELECT f1.FolderId, f1.ImageId, (SELECT MIN(f2.instertedon) FROM Favorite f2 WHERE f2.FolderId = f1.FolderId AND f2.ImageId = f1.ImageId) timeMarker
FROM Favorite f1
JOIN Customer c ON f1.CustomerId = c.CustomerId
LEFT JOIN SlideShowImage ssi ON f1.FolderId = ssi.FolderId AND f1.ImageId = ssi.ImageId
WHERE f1.FolderId = NEW.FolderId AND f1.ImageId = NEW.ImageId
AND f1.Selected = 1 AND c.IsInSlideShow = 1 AND f1.Status = 0 AND ssi.rowid IS NULL;
SELECT RAISE(IGNORE);
END;
Delete:
CREATE TRIGGER IF NOT EXISTS tu_Favorite_del_from_SlideShowImage AFTER UPDATE
ON Favorite FOR EACH ROW WHEN (NEW.Selected = 0)
BEGIN
DELETE FROM SlideShowImage WHERE rowid IN
( SELECT rowid
FROM (SELECT ssi.rowid, count(f1.instertedon) as cnt
FROM Favorite f1
JOIN SlideShowImage ssi on f1.FolderId = ssi.FolderId and f1.ImageId = ssi.ImageId
WHERE f1.FolderId = NEW.FolderId AND f1.ImageId = NEW.ImageId
GROUP BY ssi.rowid, f1.FolderId, f1.ImageId)
WHERE cnt = 0);
SELECT RAISE(IGNORE);
END
Aucun commentaire:
Enregistrer un commentaire