I currently have a database that stores some transactions for several users, for example purchasing or selling products is stored as 1 entry in the transactions table. It can also store profits made between transactions (stored in another table called profits).
I also need to store a daily history of purchases, revenues and profits for each user. I have a history table that looks something like this:
user_iduser date total_buy total_sell total_profit
2500 2016-04-02 24555 45121 2566
Right now, the way I keep the history table updated is outside the database side. The application automatically calculates and updates this table everytime the user logs in (after fetching whatever new transactions come from that day) and updates the corresponding totals for that day. The application also runs a script everyday at midnight that fetches every user's transactions and updates their totals for the previous day. However, I would prefer if this behavior was done by the database instead if possible (the less code the better).
So my question is: would creating triggers be a good way to do this, performance wise? It would be simple to create a trigger that, for example updates a user's history.total_buy column everytime a new 'buy' transaction is inserted, but considering I have several million transactions already stored and thousands of new ones arriving a day, wouldn't the constant trigger calls put a stress on the database? Or am I just getting worried for no reason?
