Update changed columns only - DOTNET

Hello,

> Is there a way in asp.net web page code to issue an UPDATE to a SQL Server
> table and only include those columns that actually changed, e.g. from a
> FormView?

What are using exactly as a Data Source for your FormView ? AFAIK Most if
not all sources should handle tracking changes in which case it should
really update only the relevant columns.

> Many times a user only changes 1 column value in a 20 column table and the
> UPDATE trigger fires each time.

Also the update trigger will always fire (it fires for an update once for
all columns) so I wonder what you are seeing (do you mean you see the
triggers fires 20 times, it should be triggered for each statement not for
each column so it would mean rather than it tries to send 20 different
update statements to the server).

If not already done I would start by using SQL Profiler to first make sure
of what is sent to the SQL Server...
--
Patrice

"Patrice" <http://scribe-en.blogspot.com/> wrote in message
news:%23IUzUy5OKHA.1372atTK2MSFTNGP02dotphx.gbl...
> Hello,
>
>> Is there a way in asp.net web page code to issue an UPDATE to a SQL
>> Server table and only include those columns that actually changed, e.g.
>> from a FormView?
>
> What are using exactly as a Data Source for your FormView ? AFAIK Most if
> not all sources should handle tracking changes in which case it should
> really update only the relevant columns.
>
>> Many times a user only changes 1 column value in a 20 column table and
>> the UPDATE trigger fires each time.
>
> Also the update trigger will always fire (it fires for an update once for
> all columns) so I wonder what you are seeing (do you mean you see the
> triggers fires 20 times, it should be triggered for each statement not for
> each column so it would mean rather than it tries to send 20 different
> update statements to the server).
>
> If not already done I would start by using SQL Profiler to first make sure
> of what is sent to the SQL Server...
> --
> Patrice
>
We are required by our auditors to record changes to certain tables and/or
columns in tables. We are creating records into audit tables that identify
user, datetime and old data value. We are currently doing that in UPDATE
triggers but I did not want records in there if someone just clicked update
on the web page but didn't really change anything or any columns being
tracked. Does this make sense?

David

> We are required by our auditors to record changes to certain tables and/or
> columns in tables. We are creating records into audit tables that identify
> user, datetime and old data value. We are currently doing that in UPDATE
> triggers but I did not want records in there if someone just clicked
> update on the web page but didn't really change anything or any columns
> being tracked. Does this make sense?

And inside the trigger do you use the UPDATE() function
(http://msdn.microsoft.com/en-us/library/ms187326.aspx) to see if a
particular column has been updated ?

For now I would like to make 100 % that you see in SQL Profiler the SQL
statement that updates all columns or if the problem could be in the trigger
code leading to believe that all columns are udpated when they are not.

To know for sure the best way is to see the SQL statement using the SQL
Profiler. Then you'll be able to know if this is a trigger side or an
ASP.NET side issue...

--
Patrice

"Patrice" <http://scribe-en.blogspot.com/> wrote in message
news:OQvy1a6OKHA.4580atTK2MSFTNGP06dotphx.gbl...
>> We are required by our auditors to record changes to certain tables
>> and/or columns in tables. We are creating records into audit tables that
>> identify user, datetime and old data value. We are currently doing that
>> in UPDATE triggers but I did not want records in there if someone just
>> clicked update on the web page but didn't really change anything or any
>> columns being tracked. Does this make sense?
>
> And inside the trigger do you use the UPDATE() function
> (http://msdn.microsoft.com/en-us/library/ms187326.aspx) to see if a
> particular column has been updated ?
>
> For now I would like to make 100 % that you see in SQL Profiler the SQL
> statement that updates all columns or if the problem could be in the
> trigger code leading to believe that all columns are udpated when they are
> not.
>
> To know for sure the best way is to see the SQL statement using the SQL
> Profiler. Then you'll be able to know if this is a trigger side or an
> ASP.NET side issue...
>
> --
> Patrice
>
>
>
Yes, I am using the UPDATE() function. I was just under the assumption that
if I issue an update SQL command and include all columns then it will change
all columns specified, even if the before and after data in the columns is
the same.

I will try the profiler and see what I come up with.

David