Sql-server – TSQL logging inside transaction

loggingsql servertransactionstsql

I'm trying to write to a log file inside a transaction so that the log survives even if the transaction is rolled back.

–start code

begin tran

insert [something] into dbo.logtable

[[main code here]]

rollback

commit

— end code

You could say just do the log before the transaction starts but that is not as easy because the transaction starts before this S-Proc is run (i.e. the code is part of a bigger transaction)

So, in short, is there a way to write a special statement inside a transaction that is not part of the transaction. I hope my question makes sense.

Best Answer

Use a table variable (@temp) to hold the log info. Table variables survive a transaction rollback.

See this article.

Related Solutions

Sql-server – How to get list of all tables in a database using TSQL

SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

To show only tables from a particular database

SELECT TABLE_NAME 
FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

Or,

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
    AND TABLE_CATALOG='dbName' --(for MySql, use: TABLE_SCHEMA='dbName' )

PS: For SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U'

Sql – Should I commit or rollback a read transaction

You commit. Period. There's no other sensible alternative. If you started a transaction, you should close it. Committing releases any locks you may have had, and is equally sensible with ReadUncommitted or Serializable isolation levels. Relying on implicit rollback - while perhaps technically equivalent - is just poor form.

If that hasn't convinced you, just imagine the next guy who inserts an update statement in the middle of your code, and has to track down the implicit rollback that occurs and removes his data.

Best Answer

Related Solutions

Sql-server – How to get list of all tables in a database using TSQL

Sql – Should I commit or rollback a read transaction

Related Topic