Re: Exception handling in Triggers.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: ben brugman (ben_at_niethier.nl)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 15:32:52 +0200

My email is

stbraslenscap@lenscaphiscom.nl

(Both lenscaps should be removed).

I tried to reply to your mail, but that bounced,
so now in the thread.

thanks in advance,
ben brugman

"Peter The Spate" <anonymous@discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@phx.gbl...
> If we can get away from the trigger for a second.
>
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
>
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
>
> So to get back to your question.
>
> SQL Server will automatically rollback transactions if the
> error is serious enough.
>
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @@TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
>
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
>
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
>
> Anyway that it. I sugest you have a play on QA.
>
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@hotmail.com (though I am a bit
> busy tonight)
>
> Peter
>
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
>
>
>
> >-----Original Message-----
> >Can anybody point me to a good text about
> >exception handling in triggers (errors and rollbacks) ?
> >
> >From 'Inside' :
> >ROLLBACK (because of a fatal error or an
> >explicit ROLLBACK command), the entire batch is aborted.
> >
> >
> >As I read this :
> >From within a trigger any FK or relational constraint
> >violation results in a fatal error aborting the complete
> >batch (and complete transaction).
> >Is this correct ?
> >
> >What if there was no transaction start ?
> >In the QA, does the QA supply a transaction
> >(increase the transaction count) if no transaction
> >was started ?
> >When sending several statements, it appears
> >that all statements are rolled back. (Are handled
> >as one transaction).
> >Does something similar happen when doing
> >similar statements from application programs ?
> >
> >Thanks for your attention,
> >
> >ben brugman.
> >
> >
> >.
> >

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... enclosing transaction or should begin and commit/rollback its own ... > I echo Ron's points and also have been using an approach very similar to> Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the> ChildSP is called from the ParentSP. ... > The ParentSp now has to ROLLBACK everything up to that point. ... As Ron> mentioned, the ParentSP may have called many child SPs, and performed many> updates up to this point, and all of these need to be rolled back. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... This will take care of what is done in the child, but what I want to do is ... rollback the entire outer transaction - the one initiated in the outer SP. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)