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 @[email protected][email protected]@error --累計是否有錯誤
update bank set currentMoney=currentMoney+1 where customerName='李四'
set @[email protected][email protected]@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