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.

You could try to [read the documentation](http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Managing_Transactions_(FireDAC)) — Alberto Martinez, Sep 11 '17 at 01:37
If that is a homework, tell your professor that storing calculated values in table fields is a bad practice. Even worse in a client application because of keeping them in sync (if stored values, then at least by triggers). — Victoria, Sep 11 '17 at 03:50
why would you store that data in the master table ? Just calculate it in the query. Very easy, always correct, less work, and so on... — GuidoG, Sep 11 '17 at 07:42
@GuidG, That's how I did it. The question here is now settled. Thank you. — Anderson, Sep 14 '17 at 01:43

GuidoG · Accepted Answer · 2017-09-11T09:04:57.093

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.

SQL Server and database transactions in Delphi

1 Answers1