Trying a transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying a transaction

Author

Topic

knight07
Starting Member

17 Posts

Posted - 2008-04-13 : 04:05:10

Hello all..
I have the following pseudocode that I used to write code in VB.Net to deal/interact with Access database. It worked fine.
Now, I'm doing the same system but with SQL server. And I thought it may be necessary to consider a "Transaction".
The system is about book evaluation, where a book info, author info, evaluator info, and evaluation value (0-9) are to be added into the database.
Here is the situation:
To add a new book evaluation, there are several possibilities depending on whether the book, author, and/or evaluator has been added before or not

Here's the pseudo code:


Private sub insertEvaluationRecord
Select Index
   case 1
     Call InsertBook
   case 2
     Call InsertBook
     Call InsertAuthor
   case 3
    Call InsertAuthor
   case 4
    Call insertBook
    Call InsertAuthor
    Call insertEvaluator
   case 5
    Call insertEvaluator
end select
   call insertEvaluation
End Sub

Private sub insertBook
 add values
End Sub

Private Sub insertAuthor
 add Values
End Sub

Private Sub insertEvaluator
 add Values
End Sub

Private Sub insertEvaluation
 add Values
End Sub

The first question is:
Do I implement a transation using a stored procedure, UDF, or is it something different?

Thanx in advance..

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 06:23:37

You don't need a transaction round a single data update so but it might be simpler if you encapsulate everything
begin try
begin tran
if index = 1
begin
exec InsertBook
end
else if index = 2
begin
exec InsertBook
exec InsertAuthor
end
else if index = 3
begin
exec InsertAuthor
end
else if index = 4
begin
exec InsertBook
exec InsertAuthor
exec insertEvaluator
end
else if index = 5
begin
exec insertEvaluator
end
exec insertEvaluation
commit tran
case 5
Call insertEvaluator
end select
call insertEvaluation
end try

begin catch
rollback tran
end catch

All the SPs need to raise an error if they fail to execute the failure code.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

knight07
Starting Member

17 Posts

Posted - 2008-04-13 : 06:55:45

Thanx nr..
Just to clear this for myself.
First: Do I add the transaction to the sp folder?
Second:
exec insertBook, exec insertAuthor, ... etc
Are these stored procedures that I create prior to creating the transaction..am I right or wrong..

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 16:15:27

The transaction is controlled by the begin, commit and rollback tran statements. Not sure what you mean by the sp folder.
Those are stored procs. They could be in-line code if they are simple though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

knight07
Starting Member

17 Posts

Posted - 2008-04-14 : 01:16:05

Thanx again Nigel..
Sorry for asking such basic questions.As I mentioned, I'm a real newbie and no matter, my questions might be awkward.

Anyways, by "sp folder" I meant where the stored procedures are usually kept.
As I see in the management studi express, for each database object-type, there is a folder. So, my question is where am I supposed to store a transaction?

Thanx again and sorry for my clumsy questions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 04:19:37

There is no such object called transaction in sql server. It depends on where you use the transaction. If you are doing it inside a sp you can find the sp at stored procedure folder. Similary your created user defined functions reside in functions folder and triggers in trigger folder beneath the table.If you are doing it adhoc by direct query you need to physically stored as a file in some common repository. It seems like what you are asking for is a version control system.Is your intention to store the query containing transaction for future reference?

knight07
Starting Member

17 Posts

Posted - 2008-04-14 : 05:23:42

Thanx visakh16:
Well, in fact I already have a database that I created with 5 tables and a few stored procedures. so I thought may be I'd better have a transaction when I need to add new book evaluation. A book evaluation, in the application I'm trying to do, requiers adding records to several tables at a time. I had general idea of what I'm doing, but I think I now know better.
I will create it in the stored procedures folder and hopefully this will work.
Thanx again for your help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 05:30:13

I think you should first read about the basics of transaction management rather than jump to coding them without properly understanding what they mean.

http://msdn2.microsoft.com/en-us/library/ms190612.aspx

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 05:43:25

Forget about folders.
You add a stored procedure to a database as an object via a create procedure script.
You can then view it via management studio or sp_helptext (or your source control system).
What you think of as the folder is just the way your client app happens to display the list of objects.

Think of an sp as a container for code.
A transaction is used to make sure that all statements inside the transaction either succeed or fail and also to hold the locks taken until the transaction completes.
As a single statement is always atomic there is no need for a transaction around a single insert/update statement.
In your case you probably need a transaction as you are inserting into many tables.

The transaction is started via a begin tran statement and terminated via a commit tran or rollback tran statement.
The transaction is active for the spid (connection) and anything that is executed on that spid will be contained within it for it's duration.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-14 : 13:11:31

It looks like your pesudo code you listed is for VB. You should look up how to handle transactions in your VB code as you are making mutiple single updates and you need to code that logic into your VB code.

knight07
Starting Member

17 Posts

Posted - 2008-04-15 : 01:51:05

Hello all..Sorry for not replying sooner, I still have problem accessing the Internet most of my time..

harsh_athalye: Thank u for your advice and for the useful link. In fact, I'm still in learning and expermenting phase with SQL. I learned about transactions through some courses that I've taken and reading a couple of books such as "SQL server 2005 for developers" by Murach. But I guess that part of learning any stuff is by trying them out for even not a true reason. The project I'm doing now is just to experiment with SQL serevr and if things dont work well with this transaction..well.. who cares? I'm just playing with it for now... and thanx again.

knight07
Starting Member

17 Posts

Posted - 2008-04-15 : 02:04:43

Thanx so much Nigel..
Now I have some light to keep going on in this path. Your two posts are very informative and I'll give it a try.

Thanx again

knight07
Starting Member

17 Posts

Posted - 2008-04-15 : 02:16:59

Lamprey.. Thank u for your feed back
You're right..the psedu code is/was for a VB code. I did the Vb code and it worked fine, but not as a transaction though. And as displayed in the psedu code, a single select-case might have 2 or 3 method calls to insert into 2 or 3 tables, and something wrong happens in the middle then some tables might not get updated with the new records.
I have no idea of how to develop a transaction in VB (as I'm also new to VB.Net), but I prefer doing it on database level. The reason is that I have a plan for later to build a web application to the same db (again for experimenting). In this case I dont have to worry about implementing the transaction again.

Thanx so much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources