Update changed columns only

[David C]

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? Many times a user only changes 1 column value in a 20 column
table and the UPDATE trigger fires each time. I was wondering if there
would be some way in a Updating event of a data source to compare before and
after values and build the UPDATE statement accordingly. Thanks.

David

 

[Patrice]

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...

 

[David C]

Hello,


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.


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...

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

 

[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?

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...

 

[David C]

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...

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