|Creating a generic audit trigger with SQL 2005 CLR||
If you are implementing an IT project of any significant size, then auditing is probably an important requirement of that project. Standard auditing usually includes tracking:
• insertions of new records,A typical audit table looks like this:
1. Audit-Id: the unique key of the audit table.The table looks simple but producing it isn't. To monitor every table in a database formerly required that you create a custom trigger for each table. Because Transact-SQL cannot inspect the metadata associated with the "inserted" and "deleted" pseudo-tables in a trigger, each trigger routine had to know the specific structure of its target table in advance. Thus despite the fact that auditing is a relatively mundane and repetitive chore, it was impossible to write a single generic routine that could function as a trigger for every table.
The new CLR integration in SQL Server 2005 changes all that because it places the full power of the CLR languages at your disposal. Without the restrictions of Transact-SQL, you can easily write a routine that can function as a trigger for every table in your database. The only per-table code you will need is a single declarative statement that associates your single CLR trigger routine with each table. That's one line of code per table rather than dozens of lines of custom code to be maintained with every table change. If you have a substantial project, that could mean a savings of dozens or perhaps even hundreds of man-hours per year.
Below is a generic CLR routine that populates an audit table having the structure shown in the example above. We will use it to produce an assembly called "AuditCommon". To produce the assembly:
Compile the above routine into an assembly. Now start up the SQL 2005 Management Studio and create a query window connected to your test database,. Begin by enabling CLR using the following Transact-SQL statements:
NOTE 2: The CLR trigger routine relies on a field naming convention to extract the target table name because the "TableName" fields (i.e. DataTable.TableName in the code) of the "inserted" and "deleted" trigger tables are not being properly populated by SQL Server with the name of the target table (ironically these pseudo-tables contain essentially all the information you could want to know about the target table except its name). The convention involves using the table name as part of the name of the first (key) field. Obviously if your database does not employ such a convention, this trigger routine will be unable to provide the table name in your audit table. We have been informed by Microsoft that in a future release, the table name may be provided somewhere within the trigger context, but this capability will not be present in the initial release of SQL Server 2005.