September 22, 2009 at 12:32 am
Hi,
I have a trigger that in turn calls a Stored procedure.There are few temp tables created within this stored procedure. Can anyone explain the behaviour of temp tables within the trigger. I need to understand how are these temp tables are handled with each trigger execution.
September 22, 2009 at 7:35 am
Temp tables that begin with one # are only available to that instances of the stored procedure. they get different names in the tempdb database so that each is unique.
Temp tables that begin with two #'s are available to a session, but probably aren't what you're dealing with.
If your trigger is calling a stored proc that generates a temp table, each of those temp tables will be created during the singular execution of that stored procedure and only available to that stored procedure during its execution. When the procedure is done executing, SQL will clean it up and drop it, or (idealy) the stored procedure's last statements will clean up after itself.
If you're worried about the stored procs stepping on each other's toes, don't be, because they won't.
September 22, 2009 at 8:14 am
Thanks a lot Mark!!!
This information gave more confidence to me 🙂
September 22, 2009 at 8:23 am
Tables with #, are local temp tables. Only available to that connection. When that connection ends, the table is gone. However, if you execute multiple things, those tables are available.
Tables with ## are global, seen by other connections. The disappear when dropped or when the instance restarts.
http://msdn.microsoft.com/en-us/library/ms174979.aspx, the remarks describe this.
However, it appears that the temp table is out of scope here with the trigger session. If I create a table inside a trigger, then it is gone when the transaction commits. This is similar to how stored procedures function. If you create a temp table inside a stored procedure, it exists for the life of that procedure. When the procedure ends, the table is gone.
If, however, you create a table outside the procedure, the tempt able is visible inside the procedure, and it will exist when the procedure ends, but be removed when your session ends.
create table MyTable (id int)
go
insert MyTable select 1
go
create trigger Mytrigger on MyTable for insert
AS
create table #test (id2 int)
insert #test select ID from inserted
return
select * from MyTable
select * from #test
go
insert MyTable select 23
go
select * from MyTable
select * from #test
go
drop table MyTable
This is in contrast to
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 22, 2009 at 8:46 am
Steve Jones - Editor (9/22/2009)
Tables with ## are global, seen by other connections. The disappear when dropped or when the instance restarts.
That's not actually correct. They disappear when the following two conditions are true:
a) The SPID responsible for their creation ends.
b) All SPIDs that have referenced them end.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 11:39 am
Sorry, Matt is correct. I've typically created them with a process that is always running at startup, so they've always appeared to be there. That's a good thing to note in that you won't be able to share the data if your connection is removed.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply