On the transaction processing and skills of database in ASP

Read the code of many friends, including some long time friends, in the processing of the database never use transactions, or some although the use of transaction processing, but did not achieve their expected effect. In fact, it is necessary to do a commercial procedure to deal with the data. Here are some issues to deal with my views on the issue.

What is called a transaction, here I am ominous, interested friends can take a look at SQL Server Book online or MSDN. Let me give you a simple example, for example, if you want to insert data from a form into two associated tables, if you do not use a transaction, then this can happen, the first table after the successful update, suddenly the database unexpected situation, the second table does not complete the operation, so there is nothing to say. To avoid this situation, you should use a transaction, which means that both tables are either successful or fail, in other words, to keep the data consistent. In this way, all operations after the begin trans are actually executed only to commit trans, and if part of the operation fails, then the entire transaction is restored with rollback trans, which is the previous state of the data holding all operations. But one of the things to be aware of here is that if a part of the operation fails, but not a fatal error, the database does not stop there, but instead continues to the next operation until the commit trans is executed, and the system performs those operations that have been successful. This is why some programs use transactions but still have some data being updated, rather than being able to maintain data consistency. So the right thing to do is to check the value of @ @error after each operation on the data, and if it goes wrong, turn to the error-handling section, which is responsible for performing rollback trans and resuming the entire transaction.

Look at the following two tables, this is a BBS registered user table and its related tables.

if exists (select * from sysobjects where ID = object_id ("Bbsuser"))
drop table Bbsuser
Go

CREATE TABLE Bbsuser
(
ID int Primary key identity NOT NULL,
UserName varchar Default "" NOT NULL,
Password varchar (a) default "" Not NULL,
Usertype tinyint default 0 NOT null--user type, 1 for moderator
Email varchar Default "" NOT NULL,
Homepage varchar default "" NOT NULL,
ICQ varchar () default "" Not NULL,
Signature varchar (255) Default "" Not NULL,--signature
Point int default 0 is not null---User points
)
Go

if exists (select * from sysobjects where ID = object_id ("bbsuseraction"))
drop table Bbsuseraction
Go

CREATE TABLE Bbsuseraction
(
ID int Primary Key identity NOT NULL,
UserID int default 0 NOT null--User ID
Signtime datetime default GETDATE () not NULL,--Logon time
IP varchar Default "" NOT NULL,--Logon IP
)
Go

The following stored procedure accomplishes the task of inserting data into the two tables after registration, and the UserID value in the second table is inserted into the automatic increment field ID in the first table, where a technique is used to use the system variable @ @identity, Its value is the value of the identity Type field in the last insert operation. This stored procedure is used in the transaction, please take a closer look at the code, I will not say more.
if exists (select * from sysobjects where ID = object_id ("Up_registeruser"))
drop proc Up_registeruser
Go

Create proc Up_registeruser
@a_strUserName varchar (@a_strPassword varchar), @a_strEmail varchar (100),
@a_strHomepage varchar (@a_strICQ varchar), @a_strSignature varchar (255),
@a_strIP varchar (15)
As
DECLARE @m_strUserID int

/* Because you want to operate on two tables, put them in a transaction.
Begin Tran-Transaction start
INSERT into Bbsuser values--Update bbsuser table
(
@a_strUserName, @a_strPassword, 0, @a_strEmail,
@a_strHomepage, @a_strICQ, @a_strSignature, 0
)
if (@ @error <> 0) Goto On_error--If the operation fails, turn to error handling
Select @m_strUserID = @ @identity--ID number inserted by Paucungang

INSERT into bbsuseraction values--Update bbsuseraction table
(
@m_strUserID, GETDATE (), @a_strIP
)
if (@ @error <> 0) Goto On_error--If the operation fails, turn to error handling

Commit Tran--end of transaction
Return (0)--Returns the success code

On_error:--Error handling
Rollback Tran--resuming transactions
Return (-1)--error code returned
Go