NEWBIE: Transactionality through stored procedures?

Dear Experts,

I may be misusing the term "transactionality", so here's the simple explanation:

I want to write a stored procedure that does this:
INSERT INTO TABLE_A VALUES "A"
INSERT INTO TABLE_B VALUES "B"

And if either fails to insert, BOTH should fail to insert.  

First, Is the use of a stored procedure a valid way to accomplish this?  

And second, will it still work if the database's recovery mode is set to simple? If not, will anything work if recovery mode is set to simple?

Thanks!
BrianMc1958




BrianMc1958Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
create procedure testSp
as
BEGIN TRAN
 INSERT INTO TableA SELECT ..
 IF @@ERROR <> 0 OR @@ROWCOUNT = 0
 BEGIN
   ROLLBACK TRAN
   RETURN
 END
 INSERT INTO TableB SELECT ..
 IF @@ERROR <> 0 OR @@ROWCOUNT = 0
 BEGIN
   ROLLBACK TRAN
   RETURN
 END


COMMIT TRAN
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rboyd56Commented:
<And second, will it still work if the database's recovery mode is set to simple? If not, will anything work if recovery mode is set to simple?>

Yes this will work if the recovery model is simple. The recovery model does not affect the types of transactions that can be used. It determines the database recoverability options.
0
BrianMc1958Author Commented:
Yikes.  I'm a newbie, and I vaguely thought that just using a stored procedure would guarantee an all-or-nothing result.  You're including explicit ROLLBACKs, which would be very complex to do in our real-world problem.  

Also, I thought ROLLBACKs were ignored if recovery mode was simple?
0
BrianMc1958Author Commented:
My comment and rboyd56's comment crossed in the mail...
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Your options are the following:

--> Manage the second insert through a stored proc
--> set up a trigger on INSERT on the first the table to automatically insert a corresponding record in the second table
--> avoid dupplication at all.  Unless imperative, data should be stored only store once in a db.  Chances are you could improve your schema by eliminating redundancy and normalization.

If you can's do normalization then go with the stored proc solution...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.