请教如何改成带事务处理的存储过程

qiuafa 2005-06-06 12:14:06

请教如何改成带事务处理的存储过程

表一
user_table (user_id_id, user_id, user_name, sex, birthdate)
user_id_id 自动增长

表二
data_table (data_id, user_id_id, instance)
data_id 自动增长

已知变量
char UserId[17];
char UserName[17];
char BirthDate[17];
char Instance[17];

TQuery *Query;
TDatabase *Database;

用法:
Database->StartTransaction();
try
{
if (InsertRecord(UserId, UserName, Birthdate, Instance))
Database->Commit();
else
Database->Rollback();
}
catch(Exception&e)
{
Database->Rollback();
}

bool InsertRecord(char* UserId, char* UserName, char* Birthdate, char* Instance)
{
AnsiString sql;
char buf[512];
int UserIdId;

// insert user
sql = AnsiString("SELECT user_id_id FROM "
"user_table WHERE user_id=\'") + UserId + '\'';
if(Query->Active) Query->Close();
Query->SQL->Text=sql;
if(!Query->Prepared) Query->Prepare();
Query->Open();
if (Query->RecordCount == 0)
{
if (Query->Active) Query->Close();
sprintf(buf, "INSERT INTO user_table (user_id,"
"user_name,sex,birth_date) VALUES ('%s',"
"'%s','%s',%s)", UserId, UserName,
Sex,BirthDate[0]?BirthDate:"NULL");
Query->SQL->Text=buf;
Query->ExecSQL();
if (Query->Active) Query->Close();
Query->SQL->Clear();
Query->SQL->Text=sql;
Query->Open();
}
if(Query->Active && Query->RecordCount==1){
Query->First();
UserIdId=Query->Fields->Fields[0]->AsInteger;
}
else
return false;
if (Query->Active) Query->Close();

//insert user data
sql = "SELECT data_id, deleted FROM data_table"
" WHERE instance=\'";
sql += Instance; sql += "\' AND user_id_id=";
sql += UserIdId;
Query->SQL->Text=sql;
Query->Open();
if (Query->RecordCount == 0) {
if (Query->Active) Query->Close();
sprintf(buf,"INSERT INTO data_table (instance, deleted, user_id_id)"
"VALUES ('%s','F',%lu)" ,Instance, UserIdId);
Query->SQL->Text=buf;
Query->ExecSQL();
}
else if(Query->RecordCount == 1) {
if (Query->Active) Query->Close();
sprintf(buf,"UPDATE data_table set deleted='F',"
"user_id_id=%lu Where instance='%s'",
UserIdId, Instance);
Query->SQL->Text=buf;
Query->ExecSQL();
}
else{
if(Query->Active) Query->Close();
return false;
}
if (Query->Active) Query->Close();
return true;
}

...全文