question

user-470 avatar image
user-470 asked

temporary tables, triggers, transactions, "database session"

I am using Sql Server 2005 I have a situation where I need the inserts of one table modification to be correlated with modifications in other tables. One approach we considered was using a temporary table to store the "correlation id" (in the example below, that would be ` Command.Id`). As long as we guaranteed that the Command table was inserted first, then the trigger on the command table could create a temporary table that held the id, which could then be accessed by the subsequent triggers on `A` and `B` as the triggers inserted on A and B into the `History` table. [ http://msdn.microsoft.com/en-us/library/ms174979.aspx][1] indicates that the local temporay table would be scoped to the current "database session". However, I am not clear on what "database session" is. It is important that the triggers all execute in the same transaction, and that if the transaction is rolled back, all the inserts created by the trigger are rolled back as well. **What is a "database session" in this context and how does it relate to transactional isolation?** (Or am I just setting myself up for a world of pain by trying to use triggers to do this?) For example: create database triggerDemo1 GO Use triggerDemo1 CREATE TABLE Command ( id int, commandName varchar(max), userId varchar(max), ) CREATE TABLE A ( id int, two char(2) ) CREATE TABLE B ( id int, balance money ) CREATE TABLE History ( id int identity, Command_id int, Table_Name varchar(max), Field_Name varchar(max), RowId int, OldValue varchar(max), NewValue varchar(max), ) --basically what I am looking for in this scenario is that when a row --gets inserted into the command table, then any inserts into tables A and B, or any updates to A or B --within the same transaction get correlated in the history table. --e.g. INSERT A(id, two) VALUES (1, 'AB') -- for sake of example, assume this row was already present. -- This is where I want to demonstrate the trigger functionality. BEGIN TRANSACTION BEGIN TRY INSERT Command (id, commandName, userId) VALUES (2, 'Blah', 992341) INSERT A (id, two) VALUES (2, 'BC') INSERT B (id, balance) VALUES (1, 100.32) UPDATE A SET two='AD' WHERE id = 1 END TRY BEGIN CATCH ROLLBACK TRANSACTION DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH COMMIT TRANSACTION --Then I would expect the History table to be populated by the trigger and show: --| id | Command_Id | Table_Name | Field_Name | RowId | OldValue | NewValue | --| (auto) | 2 | A | two | 2 | (null) | BC | --| (auto) | 2 | B | balance | 1 | (null) | 100.32 | --| (auto) | 2 | A | two | 2 | AB | AD | [1]: http://msdn.microsoft.com/en-us/library/ms174979.aspx
sql-server-2005triggertransactiontemporary-table
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
robbin avatar image
robbin answered
First of all, this simply can be acheived with `OUTPUT INTO` clause. Whatever is inserted, updated, deleted can be put into a table. Please visit [BOL][1] for more help. If you still have some ambiguities, do let me know. Secondly, all of your main concerns are answered in the same thread you have referred to > Temporary tables are automatically > dropped when they go out of scope, > unless explicitly dropped by using > DROP TABLE: > > A local temporary table created in a > stored procedure is dropped > automatically when the stored > procedure is finished. The table can > be referenced by any nested stored > procedures executed by the stored > procedure that created the table. The > table cannot be referenced by the > process that called the stored > procedure that created the table. > > All other local temporary tables are > dropped automatically at the end of > the current session. > > Global temporary tables are > automatically dropped when the session > that created the table ends and all > other tasks have stopped referencing > them. The association between a task > and a table is maintained only for the > life of a single Transact-SQL > statement. This means that a global > temporary table is dropped at the > completion of the last Transact-SQL > statement that was actively > referencing the table when the > creating session ended. > > A local temporary table created within > a stored procedure or trigger can have > the same name as a temporary table > that was created before the stored > procedure or trigger is called. > However, if a query references a > temporary table and two temporary > tables with the same name exist at > that time, it is not defined which > table the query is resolved against. > Nested stored procedures can also > create temporary tables with the same > name as a temporary table that was > created by the stored procedure that > called it. However, for modifications > to resolve to the table that was > created in the nested procedure, the > table must have the same structure, > with the same column names, as the > table created in the calling procedure Following could be what you want. After such implementation, you will not be needing the insertions into History table through triggers. BEGIN TRANSACTION BEGIN TRY INSERT Command (id, commandName, userId) VALUES (2, 'Blah', 992341) INSERT A (id, two) OUTPUT 2, 'A', 'two', INSERTED.id,NULL,INSERTED.[two] INTO [dbo].[History] ( [Command_id] ,[Table_Name] ,[Field_Name] ,[RowId] ,[OldValue] ,[NewValue] ) VALUES (2, 'BC') INSERT B (id, balance) OUTPUT 2, 'B', 'balance', INSERTED.id,NULL,INSERTED.[balance] INTO [dbo].[History] ( [Command_id] ,[Table_Name] ,[Field_Name] ,[RowId] ,[OldValue] ,[NewValue] ) VALUES (1, 100.32) UPDATE A SET two='AD' OUTPUT 2, 'A', 'two', INSERTED.id,DELETED.[two],INSERTED.[two] INTO [dbo].[History] ( [Command_id] ,[Table_Name] ,[Field_Name] ,[RowId] ,[OldValue] ,[NewValue] ) WHERE id = 1 END TRY BEGIN CATCH ROLLBACK TRANSACTION DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH COMMIT TRANSACTION [1]: http://msdn.microsoft.com/en-us/library/ms177564.aspx
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I must let you know that your current BEGIN TRANSACTION BEGIN TRY..CATCH structure is not in a proper shape.
0 Likes 0 ·
Looks like a possibility, I'll have to spend some more time looking at it. Though I'm still not clear on what a "database session" exactly is.
0 Likes 0 ·
Also, what is wrong with the BEGIN TRANSACTION BEGIN TRY..CATCH structure that I have? I thought it was pretty standard.
0 Likes 0 ·
For temporary tables, the scope is limited to the session. A session is made when you connect to the sql server instance. As long as you are connected to the instance with the same session, you can call out that specific temp table. You can pass that temp tables to stored procedures and even triggers. I would not do the latter, as it could cause some bad performance and unwanted scenarios. But I do not understand why you need a temp table? Are you trying to create a temp table in the trigger? In that case you would not be able to call that temp table in the script. The scope of that temp table would end with the execution of the trigger. And somehow, If you still want the temp table, then add that temp table in the above script. Then you would be able to refer it in all the related triggers. The scope of the temp table would remain until you disconnect your session. Hope that clears the ambiguity. As far as BEGIN TRANSACTION... structure, if the statement is rolled back, the last COMMIT TRANSACTION would still be executed and will give the error. Moreover, you have to get the Error messages filled in the variables, before the ROLLBACK statement.
0 Likes 0 ·
I believe the RAISERROR would stop the execution of the script before the COMMIT TRANSACTION was reached.
0 Likes 0 ·
@user-470 Yes, it would but then what is the point to even use the try/catch block? Usually the block is used to handle the errors and act accordingly, but in your case you simply gather the info which original error already has and then you re-throw it, that is all. I would still change your script to use the standard implementation of the try/catch meaning that COMMIT TRAN part goes inside of the TRY and the CATCH block does not just do the ROLLBACK TRAN but rather checks to see if it can afford it first, i.e
    COMMIT TRAN;
END TRY
BEGIN CATCH
    if @@trancount > 0 ROLLBACK TRAN;
    -- etc
0 Likes 0 ·
Unlike the app programming languages, there is no way to restrict the scope of the variable in T-SQL. The below executes just fine:
set nocount on;

begin try

    if 1 = 0 -- no worries, the variables will be declared anyway
    begin;
        declare @a int;
        declare @b int;
    end;
    
    select @a = 1, @b = 2;
    select @a as a, @b as b, 'try' as block;
    
    select 1/0 result; -- this forces the flow into catch block
    
end try
begin catch
    -- both variables are visible even though they 
    -- are supposed to be declared only if 1 = 0 :)
    select 
        @a * 2 as double_a, 
        @b * 2 as double_b, 'catch' as block;

    select error_message() msg;
    
end catch;
go

set nocount off;
go

-- it produces the following results:

a           b           block
----------- ----------- -----
1           2           try

result
-----------

double_a    double_b    block
----------- ----------- -----
2           4           catch

msg
--------------------------------
Divide by zero error encountered
0 Likes 0 ·
@robbin and @Oleg, your comments are quite helpful - and I now understand what is wrong with my try-catch structure. I hate to belabor the point (and perhaps I am just dense) but I'm still a little unclear on what exactly a "database session" comprises. It is clear that you can have multiple transactions in the same "database session", but is "database session" then exactly synonymous with "database connection"? Or is there just a difference when using connection pooling?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.