Timeline for Is using triggers best solution for this scenario
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2015 at 13:23 | vote | accept | ilija veselica | ||
| Apr 13, 2015 at 11:37 | answer | added | user50236 | timeline score: 1 | |
| Apr 13, 2015 at 11:27 | comment | added | user50236 | just NO! Because When record is inserted in Order table, trigger calls stored procedure which inserts record into WorkLoad table I'm looking for the article that explains why you shouldn't use stored procedures, that's why I didn't reply. Short story, you need to respect the rollbacks, by send data to a procedure, you lose that, hence data integrity hazard. | |
| Mar 17, 2015 at 16:02 | comment | added | ilija veselica | WorkLoad should be table because it's kind of staging table which is used to populate WorkLoadAggregation. I did made some changes to my initial idea - WorkLoadAggregation is actually an indexed view which queries WorkLoad table. One reason why WorkLoad can't be an indexed view is because WorkLoadAggregation indexed view can't be built on top of another view | |
| Mar 17, 2015 at 15:37 | comment | added | gvee | Why does WorkLoad have to by a physical table? Justin's suggestion is a sound one. Create a view. Making it an indexed view is optional. | |
| Mar 14, 2015 at 8:37 | answer | added | Naveed Ahmed | timeline score: 1 | |
| Mar 13, 2015 at 20:43 | comment | added | ilija veselica | @JustinCave forgot to mention that query which populates WorkLoad table is using CTE | |
| Mar 13, 2015 at 20:41 | comment | added | ilija veselica | @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...? | |
| Mar 13, 2015 at 20:38 | comment | added | ilija veselica | @ThomasStringer I am not sure I understand your answer. What is your suggestion that I should do in this case? | |
| Mar 13, 2015 at 20:37 | history | edited | ilija veselica | CC BY-SA 3.0 | deleted 1 character in body |
| Mar 13, 2015 at 20:35 | comment | added | Thomas Stringer | 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. | |
| Mar 13, 2015 at 20:32 | comment | added | ilija veselica | Sql server 2012 | |
| Mar 13, 2015 at 20:31 | review | First posts | |||
| Mar 13, 2015 at 22:51 | |||||
| Mar 13, 2015 at 20:31 | comment | added | Justin Cave | 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. | |
| Mar 13, 2015 at 20:28 | history | asked | ilija veselica | CC BY-SA 3.0 |