temporary tables, triggers, transactions, "database session"

Question

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

Answer 1

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

temporary tables, triggers, transactions, "database session"

Follow this question

Related Questions