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.