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.
INSERT Command (id, commandName, userId) VALUES (2, 'Blah', 992341)
INSERT A (id, two)
OUTPUT 2, 'A', 'two',
VALUES (2, 'BC')
INSERT B (id, balance)
OUTPUT 2, 'B', 'balance',
VALUES (1, 100.32)
UPDATE A SET two='AD'
OUTPUT 2, 'A', 'two',
WHERE id = 1
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);