[sqlite] CURRENT_TIMESTAMP value in single transaction

[Brandon, Nicholas \(UK\)]

When enclosed in a single transaction, would inserting many rows into a
table using the special default keyword 'CURRENT_TIMESTAMP' result in
all of the rows guaranteeing the same timestamp value?

If not, is there a recommended way to assign a unique value to a
collection of inserts in a single transaction generated from a trigger?

Thanks in advance
Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] CURRENT_TIMESTAMP value in single transaction

[Dennis Cote]

Brandon, Nicholas (UK) wrote:

When enclosed in a single transaction, would inserting many rows into a
table using the special default keyword 'CURRENT_TIMESTAMP' result in
all of the rows guaranteeing the same timestamp value?

If not, is there a recommended way to assign a unique value to a
collection of inserts in a single transaction generated from a trigger?


  

Brandon,

I don't think there is a recommended way, but something like this should 
work:


   begin;
   create temp table time as select julianday('now') as current;
   insert into tab1 values ((select current from time), ...);
   insert into tab2 values ((select current from time), ...);
   drop temp table time;
   commit;

Basically you need to store the timestamp you want to use for all the 
inserts in the transaction for the length of the transaction. The only 
good place to store things in sqlite is in a table.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] CURRENT_TIMESTAMP value in single transaction

[Kees Nuyt]

Hi Nick, 

On Tue, 28 Aug 2007 17:39:16 +0100, you wrote:

 When enclosed in a single transaction, would inserting
 many rows into a table using the special default keyword
 'CURRENT_TIMESTAMP' result in all of the rows
 guaranteeing the same timestamp value?

Did you try?
I did. Surprisingly, it doesn't give the same value in every
row. Perhaps it should. Joe Celko thinks it should.
(SQL for Smarties, third edition, 2005 Elsevier; 
 paragraph 4.2.3 Handling Timestamps).

 If not, is there a recommended way to assign a unique
 value to a collection of inserts in a single transaction
 generated from a trigger?

I'm sorry, I have no usable ideas at the moment.
Your problem suggests the timestamp will be used as a foreign
key. In such cases I usually use a natural key (i.e. propagate
the primary key of the referred table) or generate a synthetic
key for both in the application.

 Thanks in advance
 Nick

Regards,
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-