November 2, 2010 at 1:52 pm
Hello,
I am creating a View from a table that I can use it for reporting. In addition, I am creating an SP that will select the data from the table and refresh the VIEW with new data every time a new record is added to the table. Lastly, I am creating a TRIGGER that will cause the SP to run to update the VIEW's data when new records are added.
The reason for this is I need a calculated column that will SUM fields from the table and I don't want to add a column directly to the table. This way I can run reports from the VIEW that contains the calculations column and get all of the data I need.
Can anyone see a better way to perform this? I am new to creating VIEWS, TRIGGERS and SPs and this seemed fairly straightforward to me for some reason. Any suggestions or critiques are appreciated.
November 2, 2010 at 3:22 pm
A view is a good place to add columns and aggregations, but I don't understand why you have a trigger and stored procedure for this. Views are materialized when they are invoked in a query, there is no need to 'refresh' a view. Can you explain what you are doing in your trigger and sp?
November 2, 2010 at 3:43 pm
Thanks for your reply, John.
I just found out that I do not need to create an SP or a Trigger, by accident.
I am in a weird position here at work. We do not have a DBA but need maintenance performed on the databases and I'm the most knowledgable...unfortunately.
I thought that I would need to create a Trigger on the table to update the data in the view as new rows of data were added to the table. I just found out through testing, that is not the case.
The Stored Procedure was to populate the calculated column fields in the view based on the data in certain fields of the view.
Trial and Error is extremely painful when working on a database. I'm learning a lot and thankfully I have a TEST system to work with. The problem is that it is taking me a long time to accomplish what I want to do because I have to research everything and have almost no experience to fall back on.
November 2, 2010 at 3:49 pm
Well, they say the 2 best ways to learn are mistakes and mentors. Here at SSC, you'll find both. I will have to say though that it is much better to learn from someone elses mistakes!
The learning curve may be steep, but hang in there and keep asking questions. Use BOL for researching basic concepts and SQL Server functionality. Use SSC to ask questions, search forums, and read articles. Between the 2, you'll have everything you need to learn to be proficient in SQL Server. Good luck.
November 4, 2010 at 7:45 am
btodd 61953 (11/2/2010)
I'm learning a lot and thankfully I have a TEST system to work with. The problem is that it is taking me a long time to accomplish what I want to do because I have to research everything and have almost no experience to fall back on.
Welcome to the wonderful world of database administration.
Just for your reference, other RDBMS do allow for "materialized views" on top of the on-the-fly standard views supported by SQL Server. Either way you do not need a procedure or trigger to maintain those MV up-to-date.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply