[thelist] SQL Update CORRECTION

David Landy davidlandy at clara.co.uk
Tue Jul 6 05:47:55 CDT 2004


> Writing lots of sprocs to cater for lots of different possible updates
does
> not sound like a good way to spend your time. The alternative is that you
> somehow get the original values (either via another extraction from the
> database, or by persiting the original value someonwhere), and compare
those
> to the new values, and update the record with the values that have
changed.
> Again, for a simple app, this seems more work than necessary.

Spot on, Ken.

In a recent app I adopted a strategy of keeping the original value in a
hidden form field, then in the "gather" proc comparing each value to see if
it's changed, and building an sql UPDATE string accordingly.

You can either hand-code these "gather" procs for each page using field
names explicitly, which could be time-consuming (or better yet write your
own piece of code to generate the aspx code for each table) or you could
simply loop through the field names of the table using whatever meta-data is
available for the table (in classic ASP you can get the field names from a
recordset).

The advantage of this approach is that you reduce server load slighly, and
it's more elegant to update only the data that's changed.

The biggest plus from my point of view is the audit trail: if you keep a log
of all query strings run against your database, it's easy to see who changed
what and when.

David.

----- Original Message -----
From: Ken Schaefer <ken at adOpenStatic.com>
To: <thelist at lists.evolt.org>
Sent: 05 July 2004 02:22
Subject: Re: [thelist] SQL Update CORRECTION


> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Jason Robbins" <evolt at whisky-fudge.org.uk>
> Subject: Re: [thelist] SQL Update CORRECTION
>
>
> : Ken Moore wrote:
> : >> There is one exception, that is if you have two functions (for
> : >> example, "scatter_tablename" and "gather_tablename") where one line
> : >> loads all fields into variables and the other saves all variables
into
> : >> the table. Even then, in your calling procs, only update the data
that
> : >> has changed.
> : The only trouble with that is it means writing ever different
> : possibility as a stored procedure - unless someone knows a trick to
> : solve this problem?
> :
> : Jas
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> What is the scale of this application? If it's just a two-tier
application,
> I would write just the one sproc, and update all the fields with the new
> values (changed or not).
>
>
> Obviously YYMV and you'll need to make a call based on your application's
> requirements.
>
> Cheers
> Ken
>
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>
>



More information about the thelist mailing list