Page 1 of 1

Update Current Price Triggers

Posted: Sat Mar 02, 2013 4:01 am
by thompsonson
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.

Code: Select all

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 ;


Re: Update Current Price Triggers

Posted: Sat Mar 02, 2013 10:58 am
by Nikolay
Hi Thompsonson,

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.

Re: Update Current Price Triggers

Posted: Sat Mar 02, 2013 5:08 pm
by thompsonson
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.

Code: Select all

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 ;