SQL Server and database transactions in Delphi

Question

I have two tables in Delphi, Sales and SaleItem, they are master detail and I need to calculate the totals (SUM) of the PriceItem column in the SaleItem table and save this value in the PriceTotal column in the Sales table.

But for this I need to do using database transactions. How to do that? I have never done it before, and I did not find anything specific that would allow me to do it on the internet.

How to use transactions in Delphi? I'm using Firedac controls and SQL Server 2014.

Answer 1

you really don't need to store that data in your master table, that would be very bad design.
Just calculate it in your query that fetches the data from the master like this

select SalesID,
       OtherSalesFields,
       ( select sum(isnull(SaleItem.PriceItem, 0)) 
         from   SaleItem 
         where  SaleItem.SalesID = Sales.SalesID
       ) as TotalPriceItem
from   Sales

Now you will always have the correct value everytime you fetch this query and never have to worry about keeping the value in sync, or about transaction, or whatever

If you really must store the value in Sales (which is bad design) then do NOT EVER do this in a client because you cannot keep the value in sync. In that case make a trigger on the SalesItem table that will calculate the value and update the Sales table.