Delphi Transactions with FireDAC: Save a Balanced Journal Entry (All or Nothing)

In accounting, one rule is never broken: total debits must equal total credits. A journal entry for an electricity bill debits the expense account and credits the bank, both 120.500. If the program saves the debit line and then crashes before the credit line, the books no longer balance, and someone spends a day finding out why.

The tool that prevents this is a database transaction: a group of changes that are saved all together or not at all. This is part 6 of the Delphi for ERP series, and it shows how to save a journal entry safely with FireDAC.

If transactions are new to you, read SQL Transactions and ACID Explained Simply first. It takes five minutes.

Diagram: Delphi saves a journal entry inside a FireDAC transaction, header and lines inserted, balance checked, then Commit, or Rollback on any error

The Tables

CREATE TABLE dbo.Accounts (
    AccountID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    AccountCode VARCHAR(20)   NOT NULL UNIQUE,
    AccountName NVARCHAR(100) NOT NULL,
    IsActive    BIT           NOT NULL DEFAULT 1
);

CREATE TABLE dbo.JournalEntries (
    JournalID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    JournalNo   VARCHAR(20)   NOT NULL UNIQUE,
    JournalDate DATE          NOT NULL,
    Description NVARCHAR(200) NOT NULL
);

CREATE TABLE dbo.JournalLines (
    LineID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    JournalID INT           NOT NULL REFERENCES dbo.JournalEntries (JournalID),
    AccountID INT           NOT NULL REFERENCES dbo.Accounts (AccountID),
    Debit     DECIMAL(14,3) NOT NULL DEFAULT 0,
    Credit    DECIMAL(14,3) NOT NULL DEFAULT 0,
    CONSTRAINT CK_JournalLines_Amounts CHECK (
        Debit >= 0 AND Credit >= 0
        AND (Debit = 0 OR Credit = 0)      -- not both on one line
        AND (Debit > 0 OR Credit > 0)      -- and not neither
    )
);

CREATE INDEX IX_JournalLines_JournalID ON dbo.JournalLines (JournalID);

INSERT INTO dbo.Accounts (AccountCode, AccountName)
VALUES ('1010', N'Bank - current account'),
       ('5200', N'Electricity expense'),
       ('2100', N'Accounts payable');

The CHECK constraint makes every single line valid. But “debits = credits” is a rule about the whole entry, so no single-row constraint can enforce it. That’s the transaction’s job.

Why Not Use the Master-Detail Screen From Part 5?

In part 5, the invoice header and each line were saved as the user went. For a journal, that would put half-finished, unbalanced entries in the database while the user is still typing.

So the journal screen works differently:

  1. The user types the lines into an in-memory table on the PC. Nothing goes to SQL Server yet.
  2. When they press Save, Delphi checks the entry.
  3. Then it writes the header and all lines inside one transaction.

Step 1: Lines in a TFDMemTable

On frmJournal, drop a TFDMemTable named mtLines. In its Fields Editor, add three fields with New field (type Data):

NameType
AccountIDInteger
DebitCurrency
CreditCurrency

Add a lookup field AccountName (Key Fields AccountID, Dataset qryAccountList, Lookup Keys AccountID, Result Field AccountName), exactly as in part 4. Connect a TDataSource and a TDBGrid. For the header, use plain TEdit controls (edtJournalNo, edtDescription) and a TDateTimePicker (dtpDate).

procedure TfrmJournal.FormCreate(Sender: TObject);
begin
  qryAccountList.Open;   // SELECT AccountID, AccountCode, AccountName FROM dbo.Accounts WHERE IsActive = 1
  mtLines.CreateDataSet;   // builds the memory table from the fields you defined
end;

Step 2: Check the Entry Before Saving

procedure TfrmJournal.CheckBalanced;
var
  TotalDebit, TotalCredit: Currency;
  LineNo: Integer;
begin
  if mtLines.State in [dsEdit, dsInsert] then
    mtLines.Post;

  TotalDebit := 0;
  TotalCredit := 0;
  LineNo := 0;
  mtLines.DisableControls;
  try
    mtLines.First;
    while not mtLines.Eof do
    begin
      Inc(LineNo);
      if mtLines.FieldByName('AccountID').IsNull then
        raise Exception.CreateFmt('Line %d: choose an account.', [LineNo]);
      if (mtLines.FieldByName('Debit').AsCurrency < 0) or
         (mtLines.FieldByName('Credit').AsCurrency < 0) then
        raise Exception.CreateFmt('Line %d: amounts cannot be negative.', [LineNo]);
      if (mtLines.FieldByName('Debit').AsCurrency <> 0) =
         (mtLines.FieldByName('Credit').AsCurrency <> 0) then
        raise Exception.CreateFmt('Line %d: enter either a debit or a credit.', [LineNo]);

      TotalDebit := TotalDebit + mtLines.FieldByName('Debit').AsCurrency;
      TotalCredit := TotalCredit + mtLines.FieldByName('Credit').AsCurrency;
      mtLines.Next;
    end;
  finally
    mtLines.EnableControls;
  end;

  if LineNo < 2 then
    raise Exception.Create('A journal entry needs at least two lines.');
  if TotalDebit <> TotalCredit then
    raise Exception.CreateFmt('The entry is not balanced: debit %s, credit %s.',
      [FormatFloat('#,##0.000', TotalDebit), FormatFloat('#,##0.000', TotalCredit)]);
end;

Notice the Currency type. It stores money as an exact fixed-point number. With Double, 0.1 + 0.2 is not exactly 0.3, so a perfectly balanced entry could fail the check by 0.0000000001. With Currency, the comparison is exact. (We tested this with Free Pascal: Currency gives True, Double gives False.)

The line (Debit <> 0) = (Credit <> 0) is a compact way to say “both filled in, or both empty”. Both are wrong.

Step 3: Save Inside a Transaction

procedure TfrmJournal.SaveJournal;
var
  JournalID: Integer;
  Diff: Variant;
begin
  CheckBalanced;   // raises an exception if anything is wrong

  DM.Conn.StartTransaction;
  try
    // 1. The header. OUTPUT INSERTED returns the new identity value.
    JournalID := DM.Conn.ExecSQLScalar(
      'INSERT INTO dbo.JournalEntries (JournalNo, JournalDate, Description) ' +
      'OUTPUT INSERTED.JournalID ' +
      'VALUES (:JournalNo, :JournalDate, :Description)',
      [Trim(edtJournalNo.Text), DateOf(dtpDate.Date), Trim(edtDescription.Text)]);

    // 2. The lines.
    mtLines.DisableControls;
    try
      mtLines.First;
      while not mtLines.Eof do
      begin
        DM.Conn.ExecSQL(
          'INSERT INTO dbo.JournalLines (JournalID, AccountID, Debit, Credit) ' +
          'VALUES (:JournalID, :AccountID, :Debit, :Credit)',
          [JournalID,
           mtLines.FieldByName('AccountID').AsInteger,
           mtLines.FieldByName('Debit').AsCurrency,
           mtLines.FieldByName('Credit').AsCurrency]);
        mtLines.Next;
      end;
    finally
      mtLines.EnableControls;
    end;

    // 3. Double-check on the server, inside the same transaction.
    Diff := DM.Conn.ExecSQLScalar(
      'SELECT SUM(Debit) - SUM(Credit) FROM dbo.JournalLines WHERE JournalID = :ID',
      [JournalID]);
    if Diff <> 0 then
      raise Exception.Create('The saved entry is not balanced. Nothing was saved.');

    DM.Conn.Commit;
  except
    DM.Conn.Rollback;
    raise;   // show the original error to the user
  end;

  ShowMessage('Journal ' + Trim(edtJournalNo.Text) + ' saved.');
  mtLines.EmptyDataSet;
end;

Add System.DateUtils to the uses clause for DateOf, which drops the time part of the picker’s value.

How it works:

  • StartTransaction starts the group. Every insert after it is pending: other users can’t see it yet.
  • If any step fails (a duplicate JournalNo, a deleted account, a network error, or our own balance check), the except block calls Rollback. SQL Server undoes the header and every line already inserted. The database looks exactly as it did before.
  • Only when everything succeeds does Commit make the whole entry permanent, at once.
  • raise re-raises the original exception, so the user sees the real reason.

All values go in as parameters, never joined into the SQL text. That keeps out SQL injection and handles dates and decimals correctly. (See Parameterised Queries in Delphi.)

With ADO, the same pattern uses ADOConnection.BeginTrans, CommitTrans and RollbackTrans, and TADOQuery (or TADOCommand) for the inserts.

Keep Transactions Short

While a transaction is open, SQL Server holds locks on the rows it changed, and other users who need those rows wait. So:

  • Never show a message box or ask the user a question between StartTransaction and Commit. If the user goes to lunch, everyone else is blocked.
  • Do all the checks you can before starting the transaction (that’s why CheckBalanced runs first).
  • Make sure every path ends in Commit or Rollback. The try..except above guarantees it.

Long or forgotten transactions are the classic cause of the “everything is frozen” call. If it happens, SQL Server Blocking: How to Find the Blocking Session shows how to find it.

Don’t Delete Posted Entries: Reverse Them

In real accounting systems, a posted journal entry is never edited or deleted. If it was wrong, you post a reversing entry (the same lines with debit and credit swapped) and then a correct one. This keeps a full audit trail. Exercise 3 below builds the reversal, in a transaction of course.

Common Mistakes

  • Saving lines one by one without a transaction. A crash in the middle leaves an unbalanced entry.
  • Using Double for money. Rounding errors make balanced entries look unbalanced. Use Currency in Delphi and DECIMAL in SQL Server.
  • Forgetting Rollback in the error path. The transaction stays open, keeps its locks, and blocks other users.
  • User interaction inside the transaction. Check first, then start the transaction, then save quickly.
  • Trusting only the Delphi check. A second check inside the transaction (or in a stored procedure, as in part 7) protects you from bugs and other programs.

Practice Exercises

  1. Show a live “Difference: 0.000” label under the grid that turns red when the entry doesn’t balance.
  2. Generate the journal number automatically and safely when two users save at the same moment.
  3. Write ReverseJournal(OldID) that creates a reversing entry for an existing journal.

Answers

1. Recalculate in the memory table’s AfterPost and AfterDelete events with a loop like the one in CheckBalanced (or a FireDAC aggregate SUM(Debit) - SUM(Credit)), then:

lblDiff.Caption := 'Difference: ' + FormatFloat('#,##0.000', TotalDebit - TotalCredit);
if TotalDebit = TotalCredit then
  lblDiff.Font.Color := clGreen
else
  lblDiff.Font.Color := clRed;

2. Let SQL Server hand out numbers with a sequence. Each call returns a different value, even under heavy load:

CREATE SEQUENCE dbo.JournalNoSeq START WITH 1 INCREMENT BY 1;
// inside the transaction, before inserting the header
JournalNo := 'JV-' + VarToStr(DM.Conn.ExecSQLScalar(
  'SELECT NEXT VALUE FOR dbo.JournalNoSeq'));   // VarToStr is in System.Variants

Sequences can leave gaps (for example after a rollback). If your auditors require gap-free numbers, use a counter table updated inside the same transaction instead.

3.

procedure TfrmJournal.ReverseJournal(const OldID: Integer);
var
  NewID: Integer;
begin
  DM.Conn.StartTransaction;
  try
    NewID := DM.Conn.ExecSQLScalar(
      'INSERT INTO dbo.JournalEntries (JournalNo, JournalDate, Description) ' +
      'OUTPUT INSERTED.JournalID ' +
      'SELECT ''R-'' + JournalNo, CAST(GETDATE() AS DATE), N''Reversal of '' + JournalNo ' +
      'FROM dbo.JournalEntries WHERE JournalID = :OldID',
      [OldID]);

    DM.Conn.ExecSQL(
      'INSERT INTO dbo.JournalLines (JournalID, AccountID, Debit, Credit) ' +
      'SELECT :NewID, AccountID, Credit, Debit ' +   // debit and credit swapped
      'FROM dbo.JournalLines WHERE JournalID = :OldID',
      [NewID, OldID]);

    DM.Conn.Commit;
  except
    DM.Conn.Rollback;
    raise;
  end;
end;

Summary

  • A transaction saves a group of changes all or nothing: StartTransaction, then Commit, or Rollback on any error.
  • Enter journal lines in a TFDMemTable, check them, then write everything in one short transaction.
  • Use Currency and DECIMAL for money so debit = credit comparisons are exact.
  • Check the balance in Delphi before saving and again on the server inside the transaction.
  • Keep transactions short, never wait for the user inside one, and reverse posted entries instead of deleting them.

Next in the series: calling SQL Server stored procedures from Delphi, so the posting rules live in the database.

Related articles

Scroll to Top