May 28, 2010 at 4:00 pm
When a stored procedure creates a temp table #MyTable, the table name in tempdb is made unique by adding a number, #MyTable_____________________000000004A6
What is the source of that number? If I create a temp table in a multi-user environment, is there a way to figure out what the actual table name is for my instance?
Thanks for sharing your knowledge.
May 28, 2010 at 4:08 pm
hester84 (5/28/2010)
When a stored procedure creates a temp table #MyTable, the table name in tempdb is made unique by adding a number, #MyTable_____________________000000004A6What is the source of that number? If I create a temp table in a multi-user environment, is there a way to figure out what the actual table name is for my instance?
Thanks for sharing your knowledge.
It doesn't matter - because each temp table is only available to that connection. Why do you think you need to full name of the temp table?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 4:12 pm
Jeffrey is correct. You just access it with the name you used to get to it.
If you create a global temp table it is visible to other connections, but the same thing. Access it with the name you gave it.
May 28, 2010 at 4:45 pm
The temp table is available to the procedure that created it and objects called by it.
I am trying to save an input parameter of UserId to pass to a delete trigger. The delete trigger can't see parameters, but it can see a temp table.
The problem is, a record could be deleted properly (through the stored procedure), in which case the temp table exists and I can retrieve the userid. But it could also be deleted in some other manner, e.g. a fat-fingered DBA. In that case, the temp table does not exist and the trigger blows up. Try-Catch doesn't let me bypass the error.
So my next thought is to check for table existence and use suser_sname() if the table does not exist.
This is a multi-user application, there could be many instances of the temp table in tempdb.
CREATE TABLE dbo.MyTable (Name varchar(50),
LastUpdateUser varchar(50),LastUpdateDate datetime)
GO
CREATE Trigger dbo.tD_MyTable on [dbo].[MyTable]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
--SET XACT_ABORT ON
DECLARE @LastUpdateUser nvarchar(30)
------------------------------------------------------------
-- to get actual logged-in user for programmatic deletes --
BEGIN TRY
SELECT @LastUpdateUser = LastUpdateUser FROM #DeleteUserID
IF @LastUpdateUser is NULL SET @LastUpdateUser=suser_sname()
END TRY
BEGIN CATCH
SELECT @LastUpdateUser = suser_sname()
END CATCH
------------------------------------------------------------
INSERT [dbo].[Log]
(
[Name],
[LastUpdateUser],
[LastUpdateDate],
[ActionCode],
[ActionUser],
[ActionDate]
)
SELECT
[Name],
@LastUpdateUser,
[LastUpdateDate],
'D',
SUSER_SNAME(),
GETDATE()
FROM deleted
END
GO
-- test code
INSERT INTO MyTable (Name, LastUpdateUser, LastUpdateDate)
VALUES ('Kelogg','hester84',getdate())
DELETE FROM MyTable WHERE [Name]='Kelogg'
May 28, 2010 at 4:53 pm
To check for a temp table, you can do the following:
IF object_id('tempdb..#DeleteUserID') IS NULL
BEGIN
-- your code here
END
ELSE
BEGIN
-- code here
END
However, I think you need to rethink how this is written. What if there are multiple rows available in the temp table? Which row are you going to use? How will that affect the actual delete?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2010 at 5:19 pm
If you create #temptable, each user sees their own copy. They don't see each others.
May 28, 2010 at 5:43 pm
Thanks, Jeffrey. That was the syntax I needed.
The temp table will only contain one row, which contains the @LastUpdateUser parameter passed into the stored procedure.
Insert/Update triggers can use the inserted.LastUpdateUser, but it is not available for a physical record delete.
This is meant to be copy/paste code for all tables that need to be logged, so we have a true audit history of who did what when. suser_sname() will be the domain account, so retrieving the userid is critical.
May 28, 2010 at 5:51 pm
Glad I could help - thanks for the feedback.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2010 at 9:07 am
Jeffrey Williams-493691 (5/28/2010)
To check for a temp table, you can do the following:
IF object_id('tempdb..#DeleteUserID') IS NULL
BEGIN
-- your code here
END
ELSE
BEGIN
-- code here
END
Strictly speaking, that code contains a bug, as the following script demonstrates:
CREATE PROC #DeleteUserID AS
GO
-- Bug!
IF OBJECT_ID('tempdb..#DeleteUserID') IS NULL
BEGIN
PRINT 'Not there (1)'
END
ELSE
BEGIN
PRINT 'Yep, table definitely exists (1)'
END;
-- No bug
IF OBJECT_ID(N'tempdb..#DeleteUserID', N'U') IS NULL
BEGIN
PRINT 'Not there (2)'
END
ELSE
BEGIN
PRINT 'Yep, table definitely exists (2)'
END;
GO
-- Tidy up
DROP PROC #DeleteUserID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2010 at 9:09 am
hester84 (5/28/2010)
When a stored procedure creates a temp table #MyTable, the table name in tempdb is made unique by adding a number, #MyTable_____________________000000004A6What is the source of that number?
To answer directly, it is the hexadecimal representation of the temporary table's object_id in tempdb.
Not a particularly useful thing to know, but you did ask 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2010 at 12:29 pm
Paul is correct - I get lazy and don't specify the object type all the time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2010 at 8:25 pm
Thanks for more interesting answers. And Paul, especially thank you for explaining the source of the table name! I don't need it after all, but it is satisfying to get the answer 🙂
May 31, 2010 at 4:36 pm
I couldn't wait until Tuesday, I spent Monday afternoon at work creating the delete trigger template.
It works beautifully, thanks for the correction on adding object type to the object_id test.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply