May 9, 2008 at 3:43 am
Hi,
i have a transaction, which will be rolled back, if errors occur, the errors are logged in a custom table.
My problem is, I want to keep the records in the error-table, when the main transaction will rollback.
How can I achieve that?
Thx
Tobias
May 9, 2008 at 5:35 am
Rollback the transaction before you insert into your audit table. Here's a sample using Adventureworks:
BEGIN TRY
BEGIN TRANSACTION
UPDATE [HumanResources].[Department]
SET [Name] = NULL
WHERE [Name] IS NOT NULL
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT INTO dbo.[AuditLog] (
[Command],
[PostTime],
[HostName],
[LoginName]
) VALUES (
/* Command - nvarchar(1000) */ N'Test',
/* PostTime - nvarchar(24) */ GETDATE(),
/* HostName - nvarchar(100) */ N'Something',
/* LoginName - nvarchar(100) */ N'Someone' )
END CATCH
SELECT * FROM dbo.[AuditLog]
WHERE [HostName] = 'Something'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply