Understanding conditional triggers

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Understanding conditional triggers

Sam Carleton-2
I am working on a trigger that only needs to be fired when the value for
the column 'Selected' is 1.  How exactly do I write that trigger?  Here is
what I have come up with:

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;

Pax vobiscum,
Sam Carleton
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Understanding conditional triggers

Clemens Ladisch
Sam Carleton wrote:
> I am working on a trigger that only needs to be fired when the value for
> the column 'Selected' is 1.  How exactly do I write that trigger?  Here is
> what I have come up with:
>
> ... WHEN (NEW.Selected = 1) ...

Looks OK.  What is the problem with that?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Understanding conditional triggers

R Smith
In reply to this post by Sam Carleton-2


On 2016/03/03 2:55 AM, Sam Carleton wrote:

> I am working on a trigger that only needs to be fired when the value for
> the column 'Selected' is 1.  How exactly do I write that trigger?  Here is
> what I have come up with:
>
> 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;

Does this trigger not compute, is not accepted, or does not work once
installed? What is the problem exactly?

 From a cursory glance, I can tell you that the this line:

"    SELECT f1.FolderId, f1.ImageId, (SELECT MIN(f2.instertedon) FROM"

messes with my OCD, which might be a clue - did you mean to spell
"Inserted On" like "f2.instertedon"?

Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Understanding conditional triggers

Sam Carleton-2
>
> Does this trigger not compute, is not accepted, or does not work once
> installed? What is the problem exactly?
>

It works fine.  Initially I was not able to find any good examples of a
conditional trigger but after a lot more searching I was able to find one
that validated that it might work, so I finally tried it and it did work.
All in all I am so use to everything in SQL having to be result set based
it looked to simple to work, so I got cold feet.


>
> From a cursory glance, I can tell you that the this line:
>
> "    SELECT f1.FolderId, f1.ImageId, (SELECT MIN(f2.instertedon) FROM"
>
> messes with my OCD, which might be a clue - did you mean to spell
> "Inserted On" like "f2.instertedon"?


That piece is correct.  The whole idea is this:  the favorites table is a
list of images a user has marked as favorites.  The SlideShowImage table is
a list of the images in a slide show.  The slideshow is made up of images
users marked to be in their favorites in the order that they added the
images without duplicates.  The reason for the inner select, looking for
the min(f2.instertedon) is to find the very first time the image was added
to the favorites.  The very end of the outer select does have a ssi.rowid
is null to make sure that if the image is already in the slide show, it is
not inserted again.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users