pervasive sql transaction

Hello everyone,

I am somewhat new to pervasive sql and I was wondering if I can run a series of sql statements as a transaction.   IE I am trying to duplicate a few records in a database with new keys and then delete the old record but only if all the inserts worked properly

insert into xtable (columna, columnb) select '12345', columnb from xtable where columna = '23456'

insert into ytable (columnd, columne, columnf) select '444444', columne, columnf from ytable where columnd = '33333'

delete from xtable where columna = '23456'
delete from ytable where columnd = '33333'

if any of those fail I want to rollback the transaction.

Any thoughts on how I would do this?

thanks in advance..
mitaiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Bill BachConnect With a Mentor PresidentCommented:
A SELECT @@ROWCOUNT might work for you.  You can read this into a variable, and if ROWCOUNT > 0, then the operation was successful.
0
 
Bill BachPresidentCommented:
Yes.  Issue BEGIN TRANSACTION at the beginning, and then END TRANSACTION at the end to commit it.  I believe ABORT TRANSACTION is used to abort and undo the changes.
0
 
mitaiAuthor Commented:
OK, I will try that.  How do you test to see if an error occured on each of the sql statements?
0
All Courses

From novice to tech pro — start learning today.