Your best bet is probably to put a trigger on table B to update table A. If
this approach isn't suitable, using a single stored procedure to perform
both the table B insert and the table A update would be another decent
approach. The insert and update should be wrapped in a transaction in order
to ensure that both operations succeed or fail together.
Your current approach requires two database round-trips since both the
insert and update are driven from the ASP page. Both the approaches
suggested above would allow you to accomplish the same goal with a single
call to the database.
"Brad" <_nospam_disruptedsinnerhotmail.com> wrote in message
news:%23XEuTTAnDHA.2432TK2MSFTNGP10.phx.gbl...to> Hi all,
> I have a field in Table A that must be updated whenever a record is addedbasically> Table B. Table A will always only contain one single record and one of the
> fields gets updated whenever Table B gets a record added. This ispopulated> how I currently have it in ASP:
> 1. User inputs data into a textbox in the browser;
> 2. Browser posts the textbox data to the ASP page;
> 3. ASP page creates a new record in Table B with one of its fields30> with the textbox data;
> 4. ASP page then updates the field in Table A.
> All of these operations get completed, and everything works fine for theDefined> or so users using the system.
> Currently, I #include this functionality in all of the ASP pages that need
> to perform steps 3 and 4 above. Again, no problem.
> But... I'm thinking that this type of functionality would be much better
> suited for SQL Server, rather than ASP, via a Stored Proc or a Useras> Function (UDF). I've read here that UDF's can be slow if complex
> calculations are used, which is definately not the case in this situation.
> Another thing is... if for some unforseen reason, the server crashed justwould> step 3 completed... and never got to step 4, then the data in Table Astep> not be updated, therefore not valid. SQL Server is on a different machine,
> so I'm thinking that even if the web server crashed after step 3, thatfuture.> 4 would still get completed, if done with a stored proc or a UDF.
> Basically I'm also trying to think ahead, performance-wise, to where the
> system might have to serve 2000+ users, and because of my paranioa, I want
> to make sure that I have used the best solution for now and for the> ;]
> Any insights are appreciated.