Local temporary tables within triggers, from a DSN connection - help!

Question

Local temporary tables within triggers, from a DSN connection - help!

bjh1977

Right there with Babe

Points: 740
More actions
January 11, 2006 at 10:23 am

#364642

Can anyone enlighten me about the following?
I have an Access database containing a linked table which is located on SQL Server (2000). About ten people use this Access DB.
An append query in Access throws data into this table. Anyone could run this query at any time.
A trigger on the SQL table performs various business rules, gathering info from other tables and reformatting it before updating further tables downstream.
Without going into too much detail, I want to create a local temp table within the trigger, so that downstream updates are performed in a certain way. I know that temp tables are "local" to the connection that creates them. My question is; if more than one person runs the Access append query at once, will the DSN connection be seen by SQL as one connection (therefore "sharing" the local table and causing problems), or will each user's request create separate connections as far as the local temp tables are concerned?
Cheers for any advice.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

local variable's boundary is batch. It does not matter the calls shares the same connection or not if the calls are not from the same batch.

In client side, if you donot control transaction explicitly, the default trasaction mode for most db connectivity is "auto commit", which makes each call as a batch.

Lowell SSC Guru Points: 323518 More actions · Answer 2

additionally, even though two connections might create a temp table witht he same name, #temp, they are actually stored as two different sysobjects in the tempdb database;

here's an example for query analyzer if you want to see it in action. run the same script from two different windows and you will see each temp table.

set nocount on

select name from tempdb.dbo.sysobjects where xtype='U'

select top 5 *

into #temp

from sysobjects

select name from tempdb.dbo.sysobjects where xtype='U'

results:

name

----------------------------------------------------------------------------------

name

---------------------------------------------------------------------------------

#temp_______________________________________________________________________________________________________________00000000002C

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bjh1977 Right there with Babe Points: 740 More actions · Answer 3

Thanks for that - answered my question and very informative, cheers.

Out of interest though, when you say "it doesnt matter if two calls use the same connection if they are not from the same batch", then why does query analyser complain if you create a temp table and dont delete it, then run the same code again (it says there is already an object in the database named etcetc..)? I always assumed that it was because you were using the same connection and hence the temp table wasnt being dropped. Surely each "run" is a separate batch though? Or doesnt QA work in the same way?

peterhe SSChampion Points: 11469 More actions · Answer 4

What I said is local variables (including table variable @TableName). As for local temporary table (#Table), if you define the local temporary table inside a trigger, the scope of the table is the trigger. Whenever the trigger is executed, it creates the table and drops it in the end of the trigger automatically.