I have to say that, as far as database design and good coding practices go, this solution is definitely not something that I would recommend. Your idea of having a single audit table with Row-Id (1) works only for single-column primary keys, and (2) would result in HUGE overhead for inserts, updates, and deletes whenever more than one column is affected. If I insert one row into a table that has 80 columns, your trigger would add the additional overhead of inserting 80 rows, one per column. No way would a well-educated DBA allow or encourage this to occur.

This type of coding/behavior is a more OO approach to database development and I've seen a lot of inexperienced-database-developers-who-are-good-application-developers design such tables. OO to Relational mapping is hard, no question, but a relational database is not object-oriented and, when you try to make it such (as with this generic design), you are causing more headaches than creating an audit table and trigger for each table. IMO, this is an example of the reasons most DBAs will not allow CLR code to be executed on their server. This is just an example of a .NET programmer doing something in .NET that should be done in T-SQL.

Scott Whigham

LearnSqlServer.com - tutorials for SQL Server 2005 and SQL 2000