1、这是直接在delphi中使用事务
procedure TForm1.Button1Click(Sender: TObject);
begin
ADOConnection1.BeginTrans;
Try
aq2.close;
aq2.SQL.clear;
aq2.SQL.add(‘update bank set currentMoney=currentMoney-1 where customerName=”张三”’);
aq2.ExecSQL;
aq2.sql.Clear;
aq2.sql.add(‘update bank set currentMoney=currentMoney+1 where customerName=”李四”’);
aq2.ExecSQL;
ADOConnection1.CommitTrans;
Application.MessageBox(‘提交成功’,”);
Except
ADOConnection1.RollbackTrans;
Application.MessageBox(‘提交失败’,”);
End;
end;
2、在存储过程里使用,然后再调用。
procedure TForm1.Button3Click(Sender: TObject);
begin
with aq2 do
begin
close;
SQL.Clear;
SQL.Add(‘exec mmm’);
try
ExecSQL;
Application.MessageBox(‘提交成功’,’存储过程’);
except
Application.MessageBox(‘提交失败’,’存储过程’);
end;
end;
end;
创建存储过程 代码:
create proc mmm
as
begin
/**//*–开始事务–*/
begin transaction
declare @errorSum int –定义变量,用于累计事务执行过程中的错误
set @errorSum=0
update bank set currentMoney=currentMoney-1 where customerName=’张三’
set @errorSum=@errorSum+@@error –累计是否有错误
update bank set currentMoney=currentMoney+1 where customerName=’李四’
set @errorSum=@errorSum+@@error –累计是否有错误
/**//*–根据是否有错误,确定事务是提交还是回滚–*/
if @errorSum<>0
begin
print ‘交易失败,回滚事务.’
rollback transaction
end
else
begin
print ‘交易成功,提交事务,写入硬盘,永久保存!’
commit transaction
end
print ‘查看转帐事务后的余额’
select * from bank
end
表:if exists(select* from sysobjects where name=’bank’)
drop table bank
create table bank
(
customerName char(10), –顾客姓名
currentMoney money –当前余额
)
go
/**//*–添加约束,帐户不能少于元–*/
alter table bank add
constraint CK_currentMoney check(currentMoney>=1)
/**//*–插入测试数据–*/
insert into bank(customerName,currentMoney)
select ‘张三’,1000 union
select ‘李四’,1
呵呵。。。以上存储过程是在网上找的,加以修改的。注:以在查询器中执行无误。。数据库是MSSQL2000