Commit something in nested transaction

  • tfifield

    SSCrazy Eights

    Points: 9655

    I am working with a Point of Sale package that does not have source code available and doesn't allow any mods to the package itself. There are some stored procedure hooks that the package will execute if it finds them.

    One of the stored procedures is executed before a transaction is committed. If any error is returned from the stored procedure the package will do a Rollback on the transaction that started before the procedure was execute.

    My problem is that I want to be able to do something inside the stored procedure that will be saved and then raise an error. The package software will then display the message from the RaisError and roll back the transaction.

    I need some way to save the fact that the stored procedure has been executed once with a particular error condition so that when the procedure is executed again with the same error condition, some other code can be executed.

    I know that any transaction within an outer transaction is always rolled back when the outer transaction is rolled back.

    Any ideas?

    Todd Fifield

  • twillcomp

    SSC Eights!

    Points: 989

    I was reading something about 'What use is CLR' and I'm sure one reply indicated that they used CLR for non-transactional work like you describe. I don't think they elaborated but it was described as doing an update that would otherwise get rolled-back if done in TSQL.

    cheers

    Tony

  • tfifield

    SSCrazy Eights

    Points: 9655

    Tony,

    Thanks - I'll check it out.

    Todd Fifield

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

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