Monthly Archives: November 2013

Using triggers to Keep Stock Portfolio Position History in your stock market database

If you are building a stock market portfolio database, you may want to create a table to store you current positions. You can do this by creating a simple table with the stock symbol, the quantity of stock and a transaction date. When new position is entered, you have purchased the stock. The ‘buy’ record can then be stored in a ‘history’ table using a trigger that is fired when a new record is added to the ‘positions’ table. When the stock is sold, the record is deleted. A trigger on the delete action adds a new record to the history table to indicate the stock was sold.

 

How to create a stock portfolio history table with MySQL? One method is as follows:

For example, see the screen shot below. A concise history of the transactions is produced.

Figure 1 stock portfolio position history table

 

Data definition in MySQL for the stock_portfolio_position_history table

 

The data definition in MySQL for the stock_portfolio_position_history table is described here:

 

 

 

How to set up the portfolio positions table?

 

First in MySQL create the stock positions table

 

 

 

Triggers for stock_portfolio_positions table

 

Next, create the trigger that will enter the buy record in the history table.

 

 

 

Next create the MySQL trigger used to add a ‘sell’ record in the history table.

 

 

 

Testing the stock market securities portfolio history feature

 

Now, you are ready to test. For example, for testing purposes, ‘buy’ 10 shares of IBM execute the following:

INSERT INTO test.stock_portfolio_positions (Symbol, Quantity) VALUES (‘IBM’, ’10’);

Note that because some of the fields have default values automatically generated, I do not need to specify the portfolioId value nor do I need to specify the transaction date.

Next for testing purposes, ‘sell’ the shares.

DELETE FROM test.stock_portfolio_positions WHERE PositionId=’6′;

The result should be something similar to figure 1.