我用Delphi7+SQL2000+ADOQuery1
有一组SQL语句如下:DELETE FROM 表1
DELETE FROM 表2
DELETE FROM 表3我理解是应该是当第一句执行完后执行再第二句,第二句执行完后再执行第三句,
如果当然当执行到第三句时,由于某种原因执行中断或失败,能否返回(回滚)前已执行过的DELETE操作?具体怎么实现?
有一组SQL语句如下:DELETE FROM 表1
DELETE FROM 表2
DELETE FROM 表3我理解是应该是当第一句执行完后执行再第二句,第二句执行完后再执行第三句,
如果当然当执行到第三句时,由于某种原因执行中断或失败,能否返回(回滚)前已执行过的DELETE操作?具体怎么实现?
解决方案 »
- 没有找到 dclcxSchedulerD11.bpl
- delphi精彩讨论100篇
- 一个超级简单的小问题!
- 请问DELPHIer~~~java可以和delphi结合使用吗?????????!!!!!!!!!!!!!!
- 如何改变dbctrlgrid控件中DBTEXT字体的颜色,马上给分!!
- ActiveX的事件的问题,求教高手。
- 一个关于流的问题,有程序例子,望解答:)
- 关于Frame框架的问题,很菜,(不赊帐哦)
- api 函数中有一个setparent函数,而delphi也有一个setparent函数,现在想调用 setparent api函数咋办?
- MS出了.net Borland 出了KYLIX我们要有什么打算呢
- 请问FastReport的FR_Ctrls.dcu在哪?
- 工厂资源管理计划
adoconnection1.BeginTrans;
DELETE FROM 表1
DELETE FROM 表2
DELETE FROM 表3
adoconnection1.CommitTrans;
except
adoconnection1.RollbackTrans;
end;
没在找到关于Delphi编程方面的!
先谢谢楼上的朋友,试一试1楼的方法!
function TDM.ExecuteSQL(SqlCmd: string; var ErrorMSG: string): boolean;
begin
Result := true;
with TADOQuery.Create(nil) do
begin
try
try
Connection := adoConn;
SQL.Text := SqlCmd;
if not adoConn.InTransaction then adoConn.BeginTrans;
ExecSQL;
adoConn.CommitTrans;
Except
On E :Exception do
begin
adoConn.RollbackTrans;
ErrorMSG := E.Message;
result := false;
end;
end;
finally
Free;
end;
end;
end;
SqlCmd这个参数放的是你的删除语句,ErrorMSG这个是错误信息,你可以在调用之后取的
procedure TForm1.SpeedButton1Click(Sender: TObject);
var
TIF,SQL1,SQL2,SQL3:string;
SqlCmd,ErrorMSG:string;
begin
TIF:='SELECT TE001 FROM INVTE WHERE TE002<=''20090630''';
SQL1:='DELETE FROM INVTC WHERE TC001 IN ('+TIF+')';
SQL2:='DELETE FROM INVTD WHERE TD001 IN ('+TIF+')';
SQL3:='DELETE FROM INVTE WHERE TE001 IN ('+TIF+')';
SqlCmd:=SQL1+#13#10+SQL3+#13#10+SQL3;
ExecuteSQL(SqlCmd,ErrorMSG);
ShowMessage(' 已删除数据库相应的记录! ');
end;我测试时将数据库INVTD表直接删了,再执行上面的程序代码,执行到'DELETE FROM INVTD WHERE TD001 IN ……时找不到INVTD表,应该报错才对,并且不再往下面的语句执行,而且第一步执行了'DELETE FROM INVTC WHERE TC001 ……也应该撤消操作(还原数据)才对,我不知道哪里错了,请大家多多指教!
但时我实际需要将三个表的操作看成一个整体,任何一个表操作有问题,其余两个表的已操作的数据也得自动恢复!这样才能保证数据的完整性!!
对SQL数据库的操作可能是删除、更新、插入数据混合使用!
var
TIF,SQL1,SQL2,SQL3:string;
begin
Screen.Cursor:=crHourGlass; //屏幕光标显砂漏状态
TIF:='SELECT TE001 FROM INVTE WHERE TE002<='+QuotedStr(Trim(FormatDateTime('yyyyMMdd',DateTimePicker1.Date)));
SQL1:='DELETE FROM INVTC WHERE TC001 IN ('+TIF+')';
SQL2:='DELETE FROM INVTD WHERE TD001 IN ('+TIF+')';
SQL3:='DELETE FROM INVTE WHERE TE001 IN ('+TIF+')';
try
ADOConnection1.BeginTrans; //开始事务
with Adoquery1 do
begin
Close; //插入记录
SQL.Clear;
SQL.Text:=SQL1+#13#10+SQL3+#13#10+SQL3;
ExecSQL;
ShowMessage(' 已删除数据库相应的记录! ');
end;
ADOConnection1.CommitTrans; //提交事务
except
On E:Exception do
begin
ADOConnection1.RollbackTrans;
Showmessage('删除失败:'+E.Message);
end;
end;
Screen.Cursor:=crDefault; //屏幕光标恢复正常状态
end;
ADOConnection1.BeginTrans; //开始事务
with Adoquery1 do
begin
Close; //插入记录
SQL.Clear;
SQL.Text:=SQL1
ExecSQL;
ShowMessage(' 已删除数据库相应的记录! ');
Close; //插入记录
SQL.Clear;
SQL.Text:=SQL2
ExecSQL;
ShowMessage(' 已删除数据库相应的记录! ');
Close; //插入记录
SQL.Clear;
SQL.Text:=SQL3
ExecSQL;
ShowMessage(' 已删除数据库相应的记录! ');
end;
ADOConnection1.CommitTrans; //提交事务
except
On E:Exception do
begin
ADOConnection1.RollbackTrans;
Showmessage('删除失败:'+E.Message);
end;
end;
ADOConnection1.BeginTrans; //开始事务 把ADOConnection1.BeginTrans; 放在try外面。
begin
Result:=False;
with qry1 do begin
close;
sql.Text:=str;
Open;
if not IsEmpty then
Result:=True;
end;
end;调用:procedure TForm1.btn1Click(Sender: TObject);
var
TIF,SQL1,SQL2,SQL3,sqly1,sqly2,sqly3:string;
begin
TIF:='SELECT TE001 FROM INVTE WHERE TE002 <=''20090630''';
SQL1:='DELETE FROM INVTC WHERE TC001 IN ('+TIF+')';
SQL2:='DELETE FROM INVTD WHERE TD001 IN ('+TIF+')';
SQL3:='DELETE FROM INVTE WHERE TE001 IN ('+TIF+')';
sqly1:='select 1 from INVTC WHERE TC001 IN ('+TIF+')';
sqly2:='select 1 from INVTd WHERE Td001 IN ('+TIF+')';
sqly3:='select 1 from INVTe WHERE Te001 IN ('+TIF+')';
if Shyes(sqly1) and Shyes(sqly2) and Shyes(sqly3) then begin
with qry2 do begin
Close;
SQL.Text:=SQL1;
ExecSQL;
Close;
SQL.Text:=SQL2;
ExecSQL;
Close;
SQL.Text:=SQL3;
ExecSQL;
end;
end;
end;
总结:
事务不是这样用的.
对楼主的意思理解:
被删的三个表,都必须有记录与INVTE表的对应,三个表中,任何一个表没相关对应字段,其它两个表都不能被删.
var
SQLStr,Record1,Record2,Record3:string;
begin
Screen.Cursor:=crHourGlass; //屏幕光标显砂漏状态
SQLStr:='SELECT TE001 FROM INVTE WHERE TE002<='+QuotedStr(Trim(FormatDateTime('yyyyMMdd',DateTimePicker1.Date)));
ADOConnection1.BeginTrans; //开始事务
try
with Adoquery1 do
begin
Close;
SQL.Clear;
SQL.Text:='DELETE FROM INVTC WHERE TC001 IN ('+SQLStr+')';
Record1:=inttostr(ExecSQL);
Close;
SQL.Clear;
SQL.Text:='DELETE FROM INVTD WHERE TD001 IN ('+SQLStr+')';
Record2:=inttostr(ExecSQL);
Close;
SQL.Clear;
SQL.Text:='DELETE FROM INVTE WHERE TE001 IN ('+SQLStr+')';
Record3:=inttostr(ExecSQL);
end;
ADOConnection1.CommitTrans; //提交事务
ShowMessage(' 已删除数据库'+inttostr(strtoint(Record1)+strtoint(Record2)+strtoint(Record3))+'行记录! ');
except
On E:Exception do
begin
ADOConnection1.RollbackTrans;
Showmessage(' 删除失败:'+E.Message);
end;
end;
Screen.Cursor:=crDefault; //屏幕光标恢复正常状态
end;
我在设想,如果在客户端执行这个程序时,在执行过程中如果突然停电(当然服务器有UPS电源),或者中断执行,或者网络中断之类的情况,程序执行了一部分就不完整了,像这样的情况该如何处理?
就不执行,这时也不用回滚了.3.当条件成立,网络正常,没有断电,加一个事务,就保证了在执行语句时发生错误能有效地回滚.4.你之前写的事务是不管条件成立与否,只要在删除数据时不发生错误就执行删除.5.原谅本人多言了,有点失礼,抱歉.
我不知道是不是这样改的,麻烦你帮我指导一下:
Function TForm1.Judge(Str: string): Boolean;
begin
Result:=False;
with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Text:=Str;
Open;
if not IsEmpty then
Result:=True;
end;
end;
procedure TForm1.SpeedButton1Click(Sender: TObject);
var
Check1,Check2,Check3:string;
SQLStr,Record1,Record2,Record3:string;
begin
Screen.Cursor:=crHourGlass; //屏幕光标显砂漏状态
SQLStr:='SELECT TE001 FROM INVTE WHERE TE002<='+QuotedStr(Trim(FormatDateTime('yyyyMMdd',DateTimePicker1.Date)));
Check1:='SELECT 1 FROM INVTC WHERE TC001 IN ('+SQLStr+')';
Check2:='SELECT 1 FROM INVTD WHERE TC001 IN ('+SQLStr+')';
Check3:='SELECT 1 FROM INVTE WHERE TC001 IN ('+SQLStr+')';
if Judge(Check1) and Judge(Check2) and Judge(Check3) then
begin
ADOConnection1.BeginTrans; //开始事务
try
with Adoquery1 do
begin
Close;
SQL.Clear;
SQL.Text:='DELETE FROM INVTC WHERE TC001 IN ('+SQLStr+')';
Record1:=inttostr(ExecSQL);
Close;
SQL.Clear;
SQL.Text:='DELETE FROM INVTD WHERE TD001 IN ('+SQLStr+')';
Record2:=inttostr(ExecSQL);
Close;
SQL.Clear;
SQL.Text:='DELETE FROM INVTE WHERE TE001 IN ('+SQLStr+')';
Record3:=inttostr(ExecSQL);
end;
ADOConnection1.CommitTrans; //提交事务
ShowMessage(' 已删除数据库'+inttostr(strtoint(Record1)+strtoint(Record2)+strtoint(Record3))+'行记录! ');
except
On E:Exception do
begin
ADOConnection1.RollbackTrans;
Showmessage(' 删除失败:'+E.Message);
end;
end;
end;
Screen.Cursor:=crDefault; //屏幕光标恢复正常状态
end;