Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1261
  • Last Modified:

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..
0
mitai
Asked:
mitai
  • 2
1 Solution
 
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
 
Bill BachPresidentCommented:
A SELECT @@ROWCOUNT might work for you.  You can read this into a variable, and if ROWCOUNT > 0, then the operation was successful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now