1 stored procedure to update 7 tables?

msmeth
10-21-2004, 12:55 PM
I've got one parent table with 6 children tables. Data for the tables are entered into a grid through a VB6 program. What I'd like to know is if its better I write one stored procedure to insert/update all these tables or write an individual SP for each table, checking that the record exists in the parent first?

HardCode
10-21-2004, 01:29 PM
If it is one process in the real world, then I would write one stored procedure. Use a Transaction so that if one action fails, you can rollback the other actions ... assuming this is what you would need. It would really be determined by your business rules. Also, if you need the same, individual action other times in your app (to update only one table for instance), then you can write 6 separate SPs to call individually as needed, and write one SP that calls all six together in a Transaction for the original purpose as described in your post.

msmeth
10-21-2004, 01:40 PM
If it is one process in the real world, then I would write one stored procedure. Use a Transaction so that if one action fails, you can rollback the other actions ... assuming this is what you would need. It would really be determined by your business rules. Also, if you need the same, individual action other times in your app (to update only one table for instance), then you can write 6 separate SPs to call individually as needed, and write one SP that calls all six together in a Transaction for the original purpose as described in your post.

Can you show me an example of using a transaction?

Its not one process in the real world. It seems to be a collection of processes. For instance, something is taken out or something is added in or a measurement is taken...its hard to explain without getting into details about the background process.

I have this grid and the user changes/enters data into the grid and its to be stored in the appropriate tables, but obviously if the record doesn't exist in the parent, it has to be inserted before it can be added to a child. You're saying use 6 individual SPs for this?

Also, would I need triggers in this at all? I'm not sure what triggers are for really...

loquin
10-21-2004, 02:32 PM
OK. IF you have 7 tables that need to be updated, it may be a good idea to have one SP for each table for the update, and one SP for each table for the insert. OR, have the update SP check to see if the data already exists, and if not, INSERT.

In any event, your SP that would be triggered from the datagrid update would accept (at least) 7 parameters, and dole out the updates to the appropriate 7 table SPs.

As far as triggers. They are used to automatically make changes to the table, or other tables, on a change to the table where they are located.

For instance, suppose that I need to keep track of the last time a record is added and changed, and by whom. So, I add 4 fields to the table. DateAdd, WhoAdd, DateMod, and WhoMod. I could let the application do this, but, it allows anyone with a knowledge of databases and/or programming to alter the audit trail. Instead, why not add a trigger to the table, and if the record is added, update the DateAdd field with the system time, and the WhoAdd field with the session user name. If the record is altered, update the other two fields in a similar fashion.

This way, EVEN IF an unscrupulous user were to try to update the user or timestamp surrupticiously, the actual values would be written into the record by the trigger in the database.

Another common use for a trigger is to create child records when a parent is created. For instance, I've got an app that generates summary data for HAZMAT reports. When I create a yearly summary record (one per hazardous material tracked,) there must be 12 monthly summaries generated as well. Rather than including this in my app, I added a trigger to the yearly summary table, so that when a record is added, 12 child records are created in the monthly record table. Conversely, when a monthly record is modified, I've got a trigger that summarizes the twelve months of data, and updates the yearly total record associated with that child record.

This means that my app, or any app that would need to update the database is simpler. I've hidden some of the complexity associated with the recordkeeping of this system within the database. This makes the external process (My app/front end) simpler to maintain. This also makes my Boss happier, since, if I were promoted, or move on, or any number of other things happen that preclude ME working on the HAZMAT system, the external apps that need to be maintained are easier to do so. A new programmer won't need to know as much of the complexities of the database.

msmeth
10-21-2004, 03:27 PM
Wow, thanks for that explanation. I understand triggers a bit more now.

So, for my problem, you're saying have one main SP that, based on what's passed in, calls each other 7 other SPs to run the actual updates? And I am going to have one update SP for each table that runs the insert if the data doesn't exist.

loquin
10-21-2004, 03:30 PM
Wow, thanks for that explanation. I understand triggers a bit more now.

So, for my problem, you're saying have one main SP that, based on what's passed in, calls each other 7 other SPs to run the actual updates? And I am going to have one update SP for each table that runs the insert if the data doesn't exist.
In the update SP for each table, add logic to check for existance of the record in the target table. If it doesn't exist, then insert, else update.

msmeth
10-22-2004, 06:08 AM
In the update SP for each table, add logic to check for existance of the record in the target table. If it doesn't exist, then insert, else update.

I understand that. I'm asking if there will be one sort of main SP that's called from the code and, depending on those parameters, it checks and calls the appropriate one of the update SPs...

Dennis DVR
10-22-2004, 06:34 AM
I don't think one main SP would be necessary since you already have seperate SP's for each table, and having one main SP would be inappropriate since the parameter for each SP would be different depending on the number of fields and datatype of each field in the table.

msmeth
10-22-2004, 06:51 AM
I don't think one main SP would be necesary since you already have seperate SP's for each table to be updated, and having one main SP would be inappropriate since the parameter for each SP would be different depending on the number of fields and datatype of each field in the table to be updated.

So check which SP is to be used in the VB code then? That'd require me to set up a lot of code to call each of 7 procedures....

Dennis DVR
10-22-2004, 07:41 AM
Right, having one main SP means that you need to pass all the required parameters for the 7 tables, and that would make your main SP hard to read and the code will be quite long, so if I were you i'll forget about the main SP thing :) .

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum