jeudi 3 mars 2016

Understanding conditional triggers

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