Quantcast

Documentation clarification request... (re: Triggers)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Documentation clarification request... (re: Triggers)

Michael Tiernan
In the online documentation: https://sqlite.org/lang_createtrigger.html

It says:

    At this time SQLite supports only FOR EACH ROW triggers, not FOR
    EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is
    optional. FOR EACH ROW implies that the SQL statements specified in
    the trigger may be executed (depending on the WHEN clause) for each
    database row being inserted, updated or deleted by the statement
    causing the trigger to fire.

So that makes sense (I think) and isn't causing a problem for me but
then, a little further on it says:

    If a WHEN clause is supplied, the SQL statements specified are only
    executed for rows for which the WHEN clause is true. If no WHEN
    clause is supplied, the SQL statements are executed for all rows.

My quandary comes from the last part of that statement "executed for all
rows". Does that mean "for all rows in the table" or does it mean "for
all rows /modified/ in the table" or "all rows previously mentioned as
being inserted, updated, or deleted by the statement causing the trigger
to fire"?

I know I can "ass-u-me" what the correct answer should be but I really
don't have the bandwidth to go and clean up the mess I might make if I'm
wrong.

Thanks for everyone's time!

--
   << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
   Non Impediti Ratione Cogatationis
   Women and cats will do as they please, and men and dogs
    should relax and get used to the idea. -Robert A. Heinlein

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

Re: Documentation clarification request... (re: Triggers)

Simon Slavin-3

On 2 Feb 2017, at 2:38pm, Michael Tiernan <[hidden email]> wrote:

> So that makes sense (I think) and isn't causing a problem for me but then, a little further on it says:
>
>   If a WHEN clause is supplied, the SQL statements specified are only
>   executed for rows for which the WHEN clause is true. If no WHEN
>   clause is supplied, the SQL statements are executed for all rows.
>
> My quandary comes from the last part of that statement "executed for all rows". Does that mean "for all rows in the table" or does it mean "for all rows /modified/ in the table" or "all rows previously mentioned as being inserted, updated, or deleted by the statement causing the trigger to fire"?

(Slight handwaving, because this probably isn’t the way things work internally.)

The check is done for each row modified.
The trigger is triggered each time the WHERE clause is satisfied.
When a trigger is triggered it is applied to one row of the table.

Each time you modify any row of any table, SQLite checks the 'WHERE' clause  each TRIGGER on that table to see whether it needs to bring that TRIGGER into play.  If that modification of that row doesn’t satisfy the WHERE clause, nothing else about the TRIGGER is processed.  If that modification does satisfy the WHERE clause then the trigger is performed on that modification.

If you issued an UPDATE command which modified six rows of the table, you should imagine the check being done six times, but the trigger would be performed any number from 0 to 6 times.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...