<div>
this solution will tell me which fields were modified but it lists multiple fields names into one cell.<br />
<br />
what I am looking for is something that will insert one record per changed field which not only tells me the field name but also tells me the old and new value... <br />
<br />
also on the update statement it updates every field regardless if the value was changed or not, will columns_updated() return those fields as updated that were not actually changed but included in the update statement?
</div>


<div>
It shouldn't be too hard for you to modify the solution I gave you in my article to accommodate what you need.
</div>


<div>
&gt;&gt;this solution will tell me which fields were modified&lt;&lt;<br />
Actually, no it does not. &nbsp;Much like the IF UPDATE(), all it tells you is that the column participated in an UPDATE statement.<br />
<br />
&gt;&gt;t lists multiple fields names into one cell.&lt;&lt;<br />
Yes, that is the way it works.<br />
<br />
&gt;&gt;what I am looking for is something that will insert one record per changed field which not only tells me the field name but also tells me the old and new value... &lt;&lt;<br />
For that you will need to use some loop to cycle through all the columns that were updated, get the column name using INFORMATION_SCHEMA.COLUMNS<wbr /> and then verify that they were modified and then do an INSERT with the before and after values. &nbsp;As I stated, you have your work cut out for you.<br />
<br />
&gt;&gt;will columns_updated() return those fields as updated that were not actually changed but included in the update statement?&lt;&lt;<br />
Yes it will. &nbsp;You will have to use the DELETED and INSERTED logicla tables to find if they were really modified.
</div>


<div>
Thanks acperkins<br />
You understand what I am facing.<br />
Since the update statement includes all fields, the columns_updated isnt really relevant in this case.<br />
<br />
Can you lead me in the right direction for query'ing the schema and then looping through the field names?
</div>


<div>
&gt;&gt;Since the update statement includes all fields, the columns_updated isnt really relevant in this case.&lt;&lt;<br />
Actually it is and is practially your only recourse.<br />
<br />
&gt;&gt;Can you lead me in the right direction for query'ing the schema and then looping through the field names?&lt;&lt;<br />
Why don't you first read the link to the article that Tim took the trouble writing.
</div>


<div>
I have read the article. &nbsp;It is very well written.<br />
<br />
However, as you stated that it will return true for every field that participated in the Update statement -- this will return true for every field for me. &nbsp;So what is the point in doing that and then writing logic to check on every field if it was modified. &nbsp;I don't understand this point.<br />
<br />
Since I'll have to do that for every field, why not save a step and loop through every field right away in the trigger?
</div>


<div>
It is very rare indeed that you update all the fields in a row. &nbsp;For example it is considered very poor practice to modify the primary key or keys and you cannot update any IDENTITY column. &nbsp;Having said that, what you will have to do is build two cursors. &nbsp;The outer cursor will cycle through all the rows in the INSERTED logical table. The inner cursor will cycle through all the columns comparing each value between the original in the DELETED table and the new in the INSERTED table. &nbsp;What you challenge is going to be is to use the INFORMATION_SCHEMA.COLUMNS<wbr /> view to get the fields names and I don't see any way to do that without using Dynamic SQL.<br />
<br />
There are two reasons I would never consider doing this:<br />
1. It is a maintenance nightmare, especially when you throw in Dynamic SQL in the mix.<br />
2. Assuming 1 is perfect, the performance on any UPDATE would be a show-stopper using two cursors in that way. &nbsp;Let's assume you update 500 rows in a table that has 20 columns that means that the cursor has to cycle 1000 times and has a potential for a 1000 inserts. &nbsp;In my view that is unacceptable.
</div>


<div>
<div class="content wysiwyg-content">
Hello, <br />
<br />
I am trying to implement the following Trigger into a table in SQL Server 2005<br />
<a href="http://jamesewelch.wordpress.com/2007/09/04/tracking-database-record-changes-at-the-field-level/" rel="ugc">http://jamesewelch.wordpress.com/2007/09/04/tracking-database-record-changes-at-the-field-level/</a><br />
<br />
I want to modify this example so not only does it check the Field in question but any field in the record...<br />
<br />
So if we have 100 fields, the trigger will loop through all fields and check for changes and log it into the Change Log table. &nbsp;I don't want to reference the actual field name anywhere -- just use something like column[i] with a while or for loop... <br />
<br />
It has to be in T-SQL because I want to keep all the code in the trigger.<br />
<br />
Thanks!
</div>
</div>


Hello, 

I am trying to implement the following Trigger into a table in SQL Server 2005
http://jamesewelch.wordpress.com/2007/09/04/tracking-database-record-changes-at-the-field-level/

I want to modify this example so not only does it check the Field in question but any field in the record...

So if we have 100 fields, the trigger will loop through all fields and check for changes and log it into the Change Log table.  I don't want to reference the actual field name anywhere -- just use something like column[i] with a while or for loop... 

It has to be in T-SQL because I want to keep all the code in the trigger.

Thanks!

Loop through Fields in a Record in T-SQL

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.