x

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
more ▼

asked Mar 07 '12 at 05:56 AM in Default

user-470 gravatar image

user-470
277 18 19 21

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Mar 07 '12 at 06:26 AM

robbin gravatar image

robbin
1.6k 1 3 5

I must let you know that your current BEGIN TRANSACTION BEGIN TRY..CATCH structure is not in a proper shape.
Mar 07 '12 at 06:55 AM robbin
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.
Mar 07 '12 at 07:05 AM user-470
Also, what is wrong with the BEGIN TRANSACTION BEGIN TRY..CATCH structure that I have? I thought it was pretty standard.
Mar 07 '12 at 07:06 AM user-470

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.
Mar 07 '12 at 07:48 AM robbin
I believe the RAISERROR would stop the execution of the script before the COMMIT TRANSACTION was reached.
Mar 07 '12 at 04:40 PM user-470
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x114
x57
x33

asked: Mar 07 '12 at 05:56 AM

Seen: 1961 times

Last Updated: Mar 08 '12 at 02:40 AM