Delphi的库存验证方案及主从表数据批量提交

本文链接：https://blog.csdn.net/daigua1987/article/details/138664444

用Delphi写系统系统的时候，难免会做库存验证，库存数据在前端的时候，很难跟后端的库存进行实时验证，这样我们就可以在保存的时候进行统一验证。
一在库存表加一个触发器，用于获取那些产品库存不足


CREATE trigger [dbo].[Sys_StockPrice] on [dbo].[Sys_Stock] for Insert,Update,Delete
as
begin
  declare @ProNoList nvarchar(max)
  select @ProNoList=ProNo+',' from Sys_Stock where Qty<0
  if isnull(@ProNoList,'')<>''
  begin
    set @ProNoList=SUBSTRING(@ProNoList,1,len(@ProNoList)-1)+'库存不足！'
    raisError(@ProNoList, 16, 1); --抛出错误
    rollback transaction --取消插入数据
  end
end

二由于我们是批量提交所以要在提交的时候加上事务，保证主从表的数据一次提交，提交失败时可以回滚，FDConnection.StartTransaction用于开启事务，FDConnection.Commit用于提交事务，FDConnection.Rollback用于回滚事务，事务里面可以嵌套事务。由于ApplyUpdates的异常无法用Try…except来抓捕，需要在TFDQuery的OnUpdateError事件里面获取错误信息，所以我们可以在OnUpdateError加入代码把错误系统获取到，可以定义一个全局变量来获取；

function TServerMethods1.ServerUpdateMoreTable(Login: string; Obj: TJSONObject;
  MainCancelStr: String; DetailCancelStr: String): String;
var
  MainSql, DetailSql: string;
  MAStream, DAStream: TStringStream;
  i,Erorrs: Integer;
  MDelta, DDelta: string;
  MainCancelList, DetailCancelList: TStringList;
begin
  MainCancelList := TStringList.Create;
  DetailCancelList := TStringList.Create;
  MainSql := Obj.GetValue('MainSql').Value;
  DetailSql := Obj.GetValue('DetailSql').Value;
  MDelta := Obj.GetValue('MDelta').Value;
  DDelta := Obj.GetValue('DDelta').Value;
  MainCancelList.CommaText := MainCancelStr;
  DetailCancelList.CommaText := DetailCancelStr;
  DSServerForm.Memo2.Lines.Add('用户：' + Login + '，日期：' +
    FormatDateTime('yyyy-MM-dd HH:nn:ss', now) + '  更新数据:' + MainSql);
  DSServerForm.Memo2.Lines.Add('用户：' + Login + '，日期：' +
    FormatDateTime('yyyy-MM-dd HH:nn:ss', now) + '  更新数据:' + DetailSql);
  Erorr := '';
  if (MainSql = '') or (MDelta = '') or (DetailSql = '') or (DDelta = '') then
  begin
    Result := '更新失败：没有接收到正确的更新数据！';
    exit;
  end;
  DATA1.StartTransaction;
  try
    Update.sql.Text := MainSql;
    MAStream := TStringStream.Create(MDelta, TEncoding.UTF8);
    MAStream.position := 0;
    Update.LoadFromStream(MAStream, sfJSON);
    for i := 0 to MainCancelList.Count - 1 do
    begin
      Update.FieldByName(MainCancelList[i]).ProviderFlags := [];
    end;
    Erorrs := Update.ApplyUpdates(0);

    UpdateD.sql.Text := DetailSql;
    DAStream := TStringStream.Create(DDelta, TEncoding.UTF8);
    DAStream.position := 0;
    UpdateD.LoadFromStream(DAStream, sfJSON);
    for i := 0 to DetailCancelList.Count - 1 do
    begin
      UpdateD.FieldByName(DetailCancelList[i]).ProviderFlags := [];
    end;
    Erorrs := UpdateD.ApplyUpdates(0);
  finally
    if (Erorrs > 0) or (Erorr<>'') then
    begin;
      DSServerForm.Memo2.Lines.Add('回滚');
      DATA1.Rollback;
    end
    else
    begin
      Data1.Commit;
      Update.CommitUpdates;
      UpdateD.CommitUpdates;
    end;
    MAStream.Free;
    DAStream.Free;
    MainCancelList.Free;
    DetailCancelList.Free;
  end;
  if Erorr <> '' then
    Result := Erorr
  else
    Result := ''
end;

procedure TServerMethods1.UpdateUpdateError(ASender: TDataSet;
  AException: EFDException; ARow: TFDDatSRow; ARequest: TFDUpdateRequest;
  var AAction: TFDErrorAction);
begin
  Erorr :=AException.Message
end;