Home SQL Server and database transactions in Delphi
Reply: 1

SQL Server and database transactions in Delphi

Anderson
1#
Anderson Published in 2017-09-10 17:15:39Z

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.

GuidoG
2#
GuidoG Reply to 2017-09-11 09:04:57Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.33905 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO