A large SQL transactional database has more than 100 tables (and it will grow). One of them is called Order. Then, there is another table WorkLoad which derives from Order and many other joined table which contains a list of all active order. Every time an order record is created, if it meets certain conditions, it should be instantly inserted into WorkLoad table. And finally, there is a third table WorkLoadAggregation which displays aggregated data grouped by date and shop and it is completely built from WorkLoad table. WorkLoadAggregation should also display live data meaning that if a record is inserted in WorkLoad table then matching date/shop aggregation should also be updated.
My idea was to handle this by triggers:
- When record is inserted in Order table, trigger calls stored procedure which inserts record into WorkLoad table
- When Order record is deleted trigger deletes the record from WorkLoad table
- When Order record is updated in a way that it doesn’t meet WorkLoad conditions, trigger deletes the record from WorkLoad table
- When record is inserted/deleted/updated in WorkLoad table, trigger calls stored procedure which updates matching date/shop aggregated record in WorkLoadAggregation table
I haven’t used triggers that much in such large transaction dbs and for such frequent calls. Is there anything bad in this approach? My biggest concern is usage of “chained triggers”, meaning that trigger on one table activates trigger on another table. I’ve been reading few articles which state that developers should be very cautious when using triggers. Are there any better solutions? Should I consider any NoSQL solution?
9
I suggest that u should follow what thomas Stringer suggested in comments.
Use store-procedures to insert update and delete from order table. In that you can call any of your tables simultaneously along with the query data and perform your transactions.
Learn more about MS-SQL Store-procedures HERE
4
I’ll discourage the option to use triggers in such case. You might be better off thinking in API terms to your model. As the comments point out, use stored procedures, as they will be easier to debug in case something goes not according to plan, keep in mind that you have a quite complex situation 😉
Some helpful references,
- wikipedia article about triggers
- nice answer in this website
- another external article about the topic