Post reply

Transactions in sql

  • August 31, 2010 at 2:16 am

    #239700

    Hi,

    In a transaction there are 3 update statements. The 2nd update statement invokes a trigger which in turn updates a audit table.

    The 3rd update fails which rolls back the transaction. So, will the record inserted by the trigger will also be rolled back?

    Regards,

    Suraj

  • August 31, 2010 at 8:03 am

    #1214316

    Have you tested this? What did you find out?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 31, 2010 at 8:22 am

    #1214340

    suraj.app (8/31/2010)

    Hi,

    In a transaction there are 3 update statements. The 2nd update statement invokes a trigger which in turn updates a audit table.

    The 3rd update fails which rolls back the transaction. So, will the record inserted by the trigger will also be rolled back?

    Regards,

    Suraj

    DECLARE @TABLE AS TABLE(

    col1 VARCHAR(5))

    BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO @TABLE

    SELECT '1'

    COMMIT TRAN

    SELECT * FROM @TABLE

    END TRY

    BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRAN

    END CATCH;

    BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO @TABLE

    SELECT '2'

    COMMIT TRAN

    SELECT * FROM @TABLE

    END TRY

    BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRAN

    END CATCH;

    BEGIN TRY

    BEGIN TRANSACTION

    SELECT 1/0

    INSERT INTO @TABLE

    SELECT '3'

    COMMIT TRAN

    SELECT * FROM @TABLE

    END TRY

    BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRAN

    END CATCH;

    SELECT * FROM @TABLE

    *not at my desk, so syntax there might be off. Hopefully you'll get the general idea though

    -Edit-

    WayneS (8/31/2010)

    skcadavre,

    This isn't really a good example, since table variables don't participate in transactions to begin with.

    Apologies, wasn't at my desk until this morning and I see you're right. Thanks for letting me know.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 31, 2010 at 9:37 am

    #1214424

    skcadavre,

    This isn't really a good example, since table variables don't participate in transactions to begin with.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 31, 2010 at 10:03 am

    #1214444

    I tried with the below code and got the result. Even the table updated by trigger will be rolled back.

    BEGIN TRAN

    DECLARE @INTERRORCODE INT

    INSERT INTO BOOKS(BOOKID,BOOKDESCRIPTION,ISBN) VALUES(2,'22','222')

    INSERT INTO BOOKS(BOOKID,ISBN) VALUES(2,'222')

    SELECT @INTERRORCODE = @@ERROR

    IF (@INTERRORCODE <> 0) GOTO PROBLEM

    COMMIT TRAN

    PROBLEM:

    IF (@INTERRORCODE <> 0) BEGIN

    PRINT 'UNEXPECTED ERROR OCCURRED!'

    ROLLBACK TRAN

    END

Viewing 5 posts - 1 through 4 (of 4 total)

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