1

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?

Improve this question
9
  • 1
    What database are you using? Many have a facility that lets the database build and automatically maintain an aggregate table. Oracle has materialized views, for example, and SQL Server has indexed views.
    – Justin Cave
    Commented Mar 13, 2015 at 20:31
  • Sql server 2012
    – ilija veselica
    Commented Mar 13, 2015 at 20:32
  • Most of your requirements can and should be implemented in the stored procedure logic and/or referential actions (i.e. when you delete a value that participates in the primary key, cascade the delete to foreign key references). Triggers can become extremely difficult to maintain and troubleshoot, especially with performance problems. I wouldn't recommend your first approach is with triggers.
    – Thomas Stringer
    Commented Mar 13, 2015 at 20:35
  • @ThomasStringer I am not sure I understand your answer. What is your suggestion that I should do in this case?
    – ilija veselica
    Commented Mar 13, 2015 at 20:38
  • @JustinCave Since these queries I use are pretty complex, I am not sure how indexed views would affect overall performance. If I run a query that populate this table at once then it runs for about 2 seconds. I am not quite familiar with how indexed view would behave in that case but I guess every time when record is inserted in Order table then indexed view query would run again fully (a second or two) and view would be updated. And then second aggregated view also...?
    – ilija veselica
    Commented Mar 13, 2015 at 20:41

2 Answers 2

Reset to default
1

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

Improve this answer
4
  • I understand stored procedures but i dont understand how can they replace triggers when it comes to triggering an event? When record is inserted in Order table how can stored procedure be automatically triggered to insert that record in other table if it's not truggered by trigger
    – ilija veselica
    Commented Mar 14, 2015 at 9:34
  • 1
    the concept is to use store-proc to add, update, delete data from your application insted of running a direct query; or you can do something like this link
    – Naveed Ahmed
    Commented Mar 14, 2015 at 9:41
  • I understand that, but i still have to use triggers that will call stored procs for crud operations, like my idea suggests? Or i am missing something here?
    – ilija veselica
    Commented Mar 14, 2015 at 9:52
  • @ile creating procedures is kind of creating APIs to your model, triggers are more likely to reinforce or help on data integrity.
    – user50236
    Commented Apr 13, 2015 at 11:29
1

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,

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.