Post reply

Transactions

  • May 9, 2008 at 3:43 am

    #190189

    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

    #812942

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply