Solved

SQL Server Stored Procedure to update records

Posted on 2013-06-27
2
341 Views
Last Modified: 2013-07-02
I have a view built that combines data for 2 tables. Its filtered to return just the records I need. I want to update data in each of the two tables (just updating two fields in each table, i an int and a varchar field). At the same time I want to insert data into a 3rd table not in the view. So for each record in the dataset, update 2 fields in each table and then insert a record into another table for each record updated. That last part of inserting I'd like to call a stored procedure that is already built. Is this possible? The goal is to update the records in bulk and capture that it was done and by who in another table. I hope this makes sense. I'm using SQL Server 2008. Thanks.
0
Comment
Question by:dodgerfan
2 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 39282568
I think it may be possible to do all that you want by using an INSTEAD OF INSERT trigger on the View.  (Note this trigger should be built on the View and not on the underlying tables.)

Here is an article to get you familiar with this concept:
http://blog.sqlauthority.com/2013/01/24/sql-server-how-to-use-instead-of-trigger-guest-post-by-vikas-munjal-koenig-solutions/
0
 
LVL 25

Expert Comment

by:jogos
ID: 39282912
The instead of trigger indeed. A little warning for triggers: make shure it works when yiu insert or update multiple rows in lne statement.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now