Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1213
  • 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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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