Local Temp Tables in Stored Proc - is it safe?

[trpnbillie]

Hi guys, I 'll try to be articulate on this one!

On Table A, I have an insert trigger that calls a stored procedure. The stored procedure contains local temp tables (#tempTable).

Considering that many records can be inserted into Table A at any time, at close intervals and by the same person, are the local temp tables safe when the stored procedure is kicked off multiple times? Do they retain their contents or can the temp tables be adversely affected. (That is, if records are inserted back to back, will the second execution of stored procedure affect the same temp table if the first execution is still running?)

I suppose I am not clear on "Connections" (if the smae trigger calls the sproc 3 times in a 2 seconds, is that 1, 2 or 3 connections?)

Can I use my local temp tables like this and be safe?

Thanks you SO much for any help!!

 

[mrdenny]

As far as your temp tables are consirned you are fine. When the trigger is fired and the procedure is run the users connection is not completed until the stored procedure is complete.

With reguard to your example I'll try to clarify for you.
If the user opens Query Analyser and runs the command in the same window it is one connection.
If the user runs the command from your app and the app disconnects after the insert is complete (and the trigger is fired, and the proc is run) then it is 3 connections.

It pretty much all depends on how your app is configured to run. But the key thing to remember is that users commands are run in what is called a Batch. So when the user does his insert within a batch, the batch does not complete until the procedure that the trigger fired off is complete.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[trpnbillie]

Thank you so much! I think I understand. Does this mean that a user cannot have more than one connection/batch at a time?

(Nothing happens in query analyzer, they are connecting to SQL via middleware)

If they do 2 inserts, the trigger for the second insert does not kick off until the trigger/sproc for the first insert is complete?

 

[mrdenny]

A user can have more than one connection if they wish. However those connections are independant of each other. Each connection can only run a single batch at a time.

Log into SQL Server with query Analyser. Then run this code.

create table #temp (t1 int)
select name from tempdb.dbo.sysobjects where xtype = 'U' and name like '#temp%'
drop table #temp

You will see the actual name of your temp table is #temp_________...____000000000{spid}{another number}. Each connection gets a spid, so if you ran the create command in two windows, you'll see two different objects with two different spids in the table name. This is how SQL tell the difference between the temp tables.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)