QA cancel

If a query is successfully cancelled in QA, does any part of it commit ?
1. if autocommit is in effect ?
2. if implicit transactions are in effect and the text in QA is as follows :
sql statement
go
commit
go
Hi,
If a query is successfully cancelled in QA, does any part of it commit ?
1. if autocommit is in effect ?
Depends up on the way you are performing the DML (Insert / update / delete)
If you are doing a row by row operation with out Begin Tran...Rollback tran
... commit tran
then SQL server does auto commit on the rows and will rollback only the DML
which was fired during the
time time you click cancel button. Rest will be successfully committed.
(If it is single Insert / Update / Delete chich handle lots of records , in
that case if you press cancel button then all the activities will be
rolled back)
2. if implicit transactions are in effect and the text in QA is as follows :
In this case if you click the cancel , data will be inside the table till
inserted / updated / deleted.
If you issue a Rollback data will be rolled back or if you issue a commit
the data will be committed.
(If it is single Insert / Update / Delete chich handle lots of records , in
that case if you press cancel button then all the activities will be
rolled back if your commit is not fired)
Thanks
Hari
MCDBA
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eiein2pGEHA.3540@.TK2MSFTNGP12.phx.gbl...
> If a query is successfully cancelled in QA, does any part of it commit ?
> 1. if autocommit is in effect ?
> 2. if implicit transactions are in effect and the text in QA is as follows
:
> sql statement
> go
> commit
> go
>

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?
A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>
|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transactio
n
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; Transaction in trigger

Hello,
I have one insert and one update in my insert update trigger. Is there any
problem if I use Begin Transaction, Rolback transaction or Commit Transaction
if both insert and update do not succeed?A Trigger is automatically enrolled in a TRANSACTION, and is committed upon
completion -so do NOT start or commit a TRANSACTION, only use ROLLBACK when
appropriate.
If you posted your proposed Trigger code, and a description of what you what
to happen or not happen, we may be able to better advise you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:683679AA-4909-468A-9088-E2A0A7BF470E@.microsoft.com...
> Hello,
> I have one insert and one update in my insert update trigger. Is there any
> problem if I use Begin Transaction, Rolback transaction or Commit
> Transaction
> if both insert and update do not succeed?
>|||You can use SAVE TRANSACTION to do a partial rollback in a trigger, assuming
the insert that fired the trigger is to be allowed, otherwise there's no
need to do any of this because a ROLLBACK in the trigger will roll back the
insert and update of the trigger and the initiating insert.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .