Thought I'd share a trigger I've added to the schema to help with stock price updates - finding this very useful when adding new stock based transactions.
CREATE TRIGGER insert_stock_current_price AFTER INSERT ON STOCK_V1
BEGIN
-- UPDATE THE CURRENTPRICE
UPDATE STOCK_V1 SET CURRENTPRICE = new.CURRENTPRICE WHERE STOCKNAME = new.STOCKNAME ;
-- NOW CALCULATE THE VALUE
UPDATE STOCK_V1 SET VALUE = CURRENTPRICE * NUMSHARES WHERE STOCKNAME = new.STOCKNAME ;
END ;
In fact we don't use all possibilities of SQLite in mmex.
SQLite used only for storage data but any other functions does not used.
When mmex app started all needed data goes to memory cache.
For all changes and calculations c++ side used then if data changed it saves to DB.
I don't know (have not enough skill) that solution is strong or weak and very difficult to change something.
yes, I had a look and saw that the code is doing the calculations. There are many good ways to skin a cat . This is basically doing the same just saves having to open each entry (as it's a pension I have one per month so there's a few already and more to come). Here's the other trigger I use to just update the CURRENTPRICE.
CREATE TRIGGER update_stock_current_price UPDATE OF CURRENTPRICE ON STOCK_V1
BEGIN
-- UPDATE THE CURRENTPRICE
UPDATE STOCK_V1 SET CURRENTPRICE = new.CURRENTPRICE WHERE STOCKNAME = new.STOCKNAME ;
-- NOW CALCULATE THE VALUE
UPDATE STOCK_V1 SET VALUE = CURRENTPRICE * NUMSHARES WHERE STOCKNAME = new.STOCKNAME ;
END ;