Solved

SQL Server Stored Procedure to update records

Posted on 2013-06-27
2
345 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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