John Mitchell-245523 (11/25/2015)
You're doing this the wrong way. First, stop using sysobjects, which is provided for backwards compatibility with SQL Server 2000 only. Second, why do you need to use sysobjects to get the table ID anyway - you know at design time what table it is since it's the table you created the trigger on. Third, don't use an EAV (entity attribute value) design for your audit table. Set it up instead so that it has the same columns as the base table (possibly repeated twice for old and new, plus a few extra for who, when and where.John
It's even worse than EAV here. They are sticking all the column names from the table into a single comma delimited list. Then I assume they are wanting to do the same to the values. I couldn't agree more that this is completely the wrong to go about this.
OP -
This is creating a very painful process to log your data. Next consider that to use this data you will be forced to unravel the mess you are shoving into the table. It doesn't save any space but it will cost you performance. Also, there really is no need to track the old AND new values every time. You are doubling the amount of logging data doing this because the next row by date will have the other part of the information.
If you really want help making a better audit of your table please post the create table statement for your base table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/