Re: sql question

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 02/16/05 

Date: Wed, 16 Feb 2005 14:36:22 -0500

tina miller via SQLMonster.com wrote:
> How are these placed exactly in the program. Do you run at 1 time or
> different?
>
> Example:
>
> begin tran
> delete
> from enc
> where epsid = '23232'
> commit
>
> I ran the above then ran the following to undo:
>
> begin tran
> rollback tran
>
> That was it. I am not entirely sure how to put the transactions
> together to enable the rollback if the data being changed it not
> correct. This is a training issue and I can't run something that is
> an else if statement. I have to delete something from a table in 1
> entry and then go back an roll it back to what it was originally in
> another entry. I need the transaction to complete being deleted and
> view the table and see it is gone and then do a rollback and then
> another select and see it is back.

The first thing you can do is use a stored procedure instead of embedded
SQL, if possible. That way, all your code is in the procedure and the
transaction can be easily managed. in your case, you would send each
command to SQL Server and then commit or rollback as needed. Your code
above doesn't seem right though. In the first batch, you issue a BEGIN
TRAN and a COMMIT. In the second, you show a BEGIN TRAN AND ROLLBACK. By
the time th esecond batch runs, the first has already committed. The
second batch does nothing since there is no SQL, other than make a
couple roundtrips to the server.

What do you mean my data being changed is not correct? Do you mean if
the delete does not affect any rows? In that case, it wouldn't hurt to
commit since nothing was changed. Can you post exactly what you need to
do and also let us know whether you can use a stored procedure instead
of batch SQL. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: DBD::Sybase and auto commit
    ... >> rollbacks into the stored proc. ... >> EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. ...
    (perl.dbi.users)
  • Re: how to roll back
    ... You can only use rollback within a transaction block. ... If you commit a ... COMMIT TRAN, then ROLLBACK TRAN to ...
    (microsoft.public.sqlserver.server)
  • Re: SP execution on rollback tran?
    ... be rolled back is one that starts with a SAVE TRAN XX command, ... Other wise a ROLLBACK by itself rolls back ALL ... A Commit will only commit the last tran. ... >> A rollback without any saved name will rollback ALL transactions for that ...
    (microsoft.public.sqlserver.programming)
  • Re: help - transaction control
    ... >transaction to successfully commit before any of the ... If a Rollback is issued anywhere along the ... > ROLLBACK TRAN ...
    (microsoft.public.sqlserver.programming)
  • Re: transaction demarction
    ... do they mark something? ... a banking transaction that moves money from one bank account ... that involves database access and ends when you either COMMIT or ROLLBACK. ...
    (comp.lang.java.programmer)