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
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
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.
3 People are following this question.
