Agregar un trigger auditoría base de datos sql server

Agregar un simple Trigger para auditar tu base de datos SQL Server
Despues de pasar muchas horas pensando, debuggiando y buscandole la vuelta a una solucion que consistia en hacerle una auditoria a una base de datos SQL 2005, pudimos desarrollar una herramienta llamada SQLTableHistory esta herramienta nos da la facilidad de que podemos seleccionar las tablas que queremos auditar.
Peronavegando me encontre con una excelente solucion hecha por Jon Galloway, en la cual consiste en crear un solo script y que este se encarge de crear los triggers necesarios para cada tabla de la base de datos seleccionada, la diferencia con SQLTableHistory es que con nuestra herramienta podemos seleccionar las tablas que queremos que se le haga su respectiva auditoria y con la solucion presentada en esteblog.
Aqui un ejemplo de como se verian los datos con la solucion de Jon Galloway

 
Con esta informacion podemos hacer reportes de una manera sencilla tales como:
 Cuales tablas han sufridos cambios recientemente.
 Cuales tablas no sufrieron cambios el pasado año.
 Cuales tablas jamaz han sufrido cambios.
 Mostrar todos los cambios a las tablas por un usuario y periodoespecifico.
 Mostrar las tablas mas activas en un determinado periodo.
Con estas herramientas es posible volver al estado anterior de la tabla y volver en un punto especifico, claro esta ya depende de las nuevas extensiones que ustedes le hagan.
Aqui os dejo el script completo que crea la tabla de auditoria y corre el script para crear los triggers a todas las tablas de la base de datos.
USEMYDATABASE--Ponerle el nombre de la base de datos que va hacer auditada
GO
 
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValuevarchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserNamevarchar(128)
)
GO
 
DECLARE @sql varchar(8000), @TABLE_NAMEsysname
SET NOCOUNT ON
 
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
 
WHILE @TABLE_NAMEIS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+'_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql =
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
 
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21),
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
 
select @TableName = ''' + @TABLE_NAME+ '''
 
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
 
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select@Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
 
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
 
-- Get primary key columns for full outer join
select@PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where...