Re: [sqlite] Order of operations when using TRIGGERs

[Dan Kennedy]

On Oct 23, 2009, at 5:05 AM, Simon Slavin wrote:

> I'm looking for assurance that things work the way I think they do,
> and will continue to do so in future versions.
>
> I'm using a TRIGGER that is triggered AFTER INSERT.  The INSERT
> commands do not know or set the value for the ROWID column.  Can I
> reliably fetch the value for this column from 'NEW.' ?

Yes. An automatically generated rowid is available in an AFTER
INSERT trigger.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of operations when using TRIGGERs

[Simon Slavin]

On 23 Oct 2009, at 3:00am, D. Richard Hipp wrote:

> On Oct 22, 2009, at 9:11 PM, Simon Slavin wrote:
>>
>
>> So now my original query comes down to this: if a column's value is
>> generated by SQLite rather than being supplied by my INSERT command,
>> i.e. if the column has a is INTEGER PRIMARY KEY, or has a DEFAULT
>> value, can I rely on being able to access that value in my TRIGGER as
>> NEW.columnName ?
>
> AFTER triggers only.  With BEFORE triggers, the NEW.columnName value
> might not have been generated yet.

Many thanks.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of operations when using TRIGGERs

[D. Richard Hipp]

On Oct 22, 2009, at 9:11 PM, Simon Slavin wrote:
>
> So now my original query comes down to this: if a column's value is
> generated by SQLite rather than being supplied by my INSERT command,
> i.e. if the column has a is INTEGER PRIMARY KEY, or has a DEFAULT
> value, can I rely on being able to access that value in my TRIGGER as
> NEW.columnName ?

AFTER triggers only.  With BEFORE triggers, the NEW.columnName value  
might not have been generated yet.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of operations when using TRIGGERs

[Simon Slavin]

On 23 Oct 2009, at 1:23am, D. Richard Hipp wrote:

> On Oct 22, 2009, at 7:11 PM, Igor Tandetnik wrote:
>>
>> In SQLite, ROWID _is_ the unique identifier for each record. Any
>> field declared INTEGER PRIMARY KEY is simply an alias for ROWID.
>> "Position in the set", whatever it is, has nothing to do with it.
>
> A clarification:
>
> The ROWID is always unique, but it not guaranteed to be static.
> ROWIDs can and often does change when you VACUUM.  Except, if you
> declare an INTEGER PRIMARY KEY, then the IPK will be the same as the
> rowid and the IPK will not change because of VACUUM.  Moral:  Always
> use INTEGER PRIMARY KEY, never a raw ROWID, for anything that you want
> to persist for longer than the current transaction.

That helps.  What you're saying is that if I ever intend to use ROWID  
I should explicitly declare a column as INTEGER PRIMARY KEY.  I can  
live with that.

So now my original query comes down to this: if a column's value is  
generated by SQLite rather than being supplied by my INSERT command,  
i.e. if the column has a is INTEGER PRIMARY KEY, or has a DEFAULT  
value, can I rely on being able to access that value in my TRIGGER as  
NEW.columnName ?

I note that the documentation about TRIGGER does warn against certain  
tricks while referring to OLD.columnName and also against other tricks  
using BEFORE TRIGGERs so I won't be doing any of that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of operations when using TRIGGERs

[D. Richard Hipp]

On Oct 22, 2009, at 7:11 PM, Igor Tandetnik wrote:
>
> In SQLite, ROWID _is_ the unique identifier for each record. Any  
> field declared INTEGER PRIMARY KEY is simply an alias for ROWID.  
> "Position in the set", whatever it is, has nothing to do with it.

A clarification:

The ROWID is always unique, but it not guaranteed to be static.   
ROWIDs can and often does change when you VACUUM.  Except, if you  
declare an INTEGER PRIMARY KEY, then the IPK will be the same as the  
rowid and the IPK will not change because of VACUUM.  Moral:  Always  
use INTEGER PRIMARY KEY, never a raw ROWID, for anything that you want  
to persist for longer than the current transaction.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of operations when using TRIGGERs

[Igor Tandetnik]

Rich Shepard  wrote:
> On Thu, 22 Oct 2009, Simon Slavin wrote:
> 
>> I'm using a TRIGGER that is triggered AFTER INSERT.  The INSERT
>> commands do not know or set the value for the ROWID column.  Can I
>> reliably fetch the value for this column from 'NEW.' ?  Or can I
>> rely only on values which are explicitly set in the INSERT command ?
> 
>   I cannot directly answer your question because I have always
> avoided using the rowid for _anything_. There's no guarantee that a
> given row will always have the same position in the set, so all the
> SQL experts I've read advise against using it. Can you assign a
> unique identifier to each row as the primary key? If so, use that in
> the trigger. 

In SQLite, ROWID _is_ the unique identifier for each record. Any field declared 
INTEGER PRIMARY KEY is simply an alias for ROWID. "Position in the set", 
whatever it is, has nothing to do with it.

To Simon: yes, in my experience, AFTER INSERT trigger can happily access 
new.ROWID and/or new.YourId (where YourId is your own column declared INTEGER 
PRIMARY KEY and thus serving as an alias for ROWID). However, not being a 
SQLite developer, I can't give any kind of official guarantee. Hopefully 
someone important will chime in.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of operations when using TRIGGERs

[Rich Shepard]

On Thu, 22 Oct 2009, Simon Slavin wrote:

> I'm using a TRIGGER that is triggered AFTER INSERT.  The INSERT commands
> do not know or set the value for the ROWID column.  Can I reliably fetch
> the value for this column from 'NEW.' ?  Or can I rely only on values
> which are explicitly set in the INSERT command ?

Simon,

   I cannot directly answer your question because I have always avoided using
the rowid for _anything_. There's no guarantee that a given row will always
have the same position in the set, so all the SQL experts I've read advise
against using it. Can you assign a unique identifier to each row as the
primary key? If so, use that in the trigger.

Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Order of operations when using TRIGGERs

[Simon Slavin]

I'm looking for assurance that things work the way I think they do,  
and will continue to do so in future versions.

I'm using a TRIGGER that is triggered AFTER INSERT.  The INSERT  
commands do not know or set the value for the ROWID column.  Can I  
reliably fetch the value for this column from 'NEW.' ?  Or can I rely  
only on values which are explicitly set in the INSERT command ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users