Crear un generador de registro de auditoría (Audit Trail) para SQL Server

Muchas veces el cliente nos pide que la aplicación que estamos desarrollando tenga registro de auditoría, o sea, que se pueda saber qué se ha cambiado en la base de datos, por quien y cuando. El método más extendido para implementar el registro de auditoría consiste en crear tablas de auditoría y triggers de auditoría. Sin embargo hay dos enfoques a lo hora de diseñar estas tablas. Uno se centra en las columnas y otro se centra en las filas. El primero genera un registro de auditoría por cada columna que se modifica y el otro genera un registro de auditoría por cada fila que se modifica. Por otro lado hay diseños que intentan ser genéricos diseñando un modelo con muy pocas tablas y otros diseños que son específicos de los datos a auditar.

A la hora de diseñar el registro de auditoría tenemos que tener en cuenta lo siguiente:

• Espacio requerido para la información de auditoría.
• Complejidad del diseño.
• Eficiencia del diseño.
• Capacidad de consulta del diseño.

Teniendo en cuenta todos estos aspectos yo me inclino por un diseño centrado en filas en el que tenemos una tabla de auditoría por cada tabla que queremos auditar y triggers de auditoría en cada tabla que queremos auditar. Esta tabla de auditoría tendrá una estructura muy similar a la tabla auditada, pero con información adicional de auditoría. A mi entender, la peor inconveniente de este diseño es que hay que crear muchas tablas y muchos triggers. Pero la buena noticia es que estas tablas y triggers se pueden crear automáticamente con un generador de sólo 147 líneas de código.

Pare entender el diseño creo que lo mejor es poner un ejemplo. Supongamos que queremos auditar la tabla Contacts:

CREATE TABLE dbo.Contacts
(
      ContactId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
      ContactName nvarchar(128) NOT NULL,
      ContactAddress nvarchar(256),
      ContactPhone varchar(50),
      ContactEmailAddress varchar(128)
)

 La tabla de auditoría dbo.ContactsAudit tendrá los mismos campos que dbo.Contacts más información de auditoría. Esta información de auditoría incluye el usuario que está realizando la modificación.  En principio podríamos usar la función ORIGINAL_LOGIN() para obtener el usuario, pero muchas veces las aplicaciones utilizan el mismo login independientemente del usuario que utiliza la aplicación, por lo que ORIGINAL_LOGIN() no nos da ninguna información acerca de ese usuario. Una solución es que la aplicación, en cuanto identifique al usuario, ponga el Id del usuario en la información de contexto, ejecutando un procedimiento almacenado como este:

CREATE PROCEDURE SetUserContext
	@UserId int
AS
	DECLARE @uc binary(4)
	SET @uc = CONVERT(binary(4), @UserId)
	SET CONTEXT_INFO @ci

 Así, puede obtenerse el Id del usuario de la siguiente manera:

DECLARE @UserId int
SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4))

 La tabla de auditoría dbo.ContactAudit incluye también el tipo de modificación (‘I’: insert, ‘U’: update, ‘D’ delete) y la fecha y hora de la modificación. Cuando se realiza una inserción, sólo registramos en la tabla dbo.ContactAudit la información adicional de auditoría, más la clave primaria, el resto de las columnas las dejamos nulas, ya que esta información ya está en la tabla dbo.Contact, no es necesario ponerla también en la tabla de auditoría, así ahorramos espacio. Cuando se hace un update o un delete, registramos en la tabla dbo.ContactAudit la información de auditoría y los valores originales de todas las columnas de dbo.Contact.

La tabla dbo.ContactAudit sería la siguiente (generado automáticamente)

CREATE TABLE dbo.ContactsAudit
(

    [_ChangeId] int IDENTITY (1,1) PRIMARY KEY,
    [_OriginalLogin] sysname,
    [_UserId] int,
    [_ChangeDate] datetimeoffset NOT NULL,
    [_ChangeType] char(1) NOT NULL,
    [ContactId] int NOT NULL ,
    [ContactName] nvarchar(128) NULL ,
    [ContactAddress] nvarchar(256) NULL ,
    [ContactPhone] varchar(50) NULL ,
    [ContactEmailAddress] varchar(128) NULL 
)

 Los triggers de audotoría  serían los siguientes (generado automáticamente):

CREATE TRIGGER dbo.Contacts_AuditTrail_Insert
ON  dbo.Contacts AFTER INSERT
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO dbo.ContactsAudit([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType], [ContactId])
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'I', [ContactId]
    FROM inserted
GO

CREATE TRIGGER dbo.Contacts_AuditTrail_Update
ON  dbo.Contacts AFTER UPDATE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO dbo.ContactsAudit([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType], [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress])
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'U', [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress]
    FROM deleted
GO

CREATE TRIGGER dbo.Contacts_AuditTrail_Delete
ON  dbo.Contacts AFTER DELETE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO dbo.ContactsAudit([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType], [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress])
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'D', [ContactId], [ContactName], [ContactAddress], [ContactPhone], [ContactEmailAddress]
    FROM deleted
GO

 El generador es una plantilla T4 de Visual Studio (2010 ó 2012). En un proyecto de Visual Studio creamos un nuevo archivo de texto llamado «AuditTrail.ttinclude» con el siguiente contenido:

<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="$(TargetPath)" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.Common" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text" #>
<#+
    public string FullTableName;
    public string FullAuditTableName;

void Render()
{
    string ConnectionString = @"Data Source=SQLDSQL2008R2;Integrated Security=SSPI;Initial Catalog=App";
    DataTable schema = null;
    StringBuilder sb = new StringBuilder();
    StringBuilder sbpk = new StringBuilder();
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand("SELECT * FROM " + FullTableName, cn))
    {
        cn.Open();
        using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
        {
            schema = reader.GetSchemaTable();
        }
    }
#>
USE App
GO
IF OBJECT_ID('<#= FullTableName  #>_AuditTrail_Insert') IS NOT NULL
	DROP TRIGGER <#= FullTableName #>_AuditTrail_Insert
GO
IF OBJECT_ID('<#= FullTableName  #>_AuditTrail_Update') IS NOT NULL
	DROP TRIGGER <#= FullTableName #>_AuditTrail_Update
GO
IF OBJECT_ID('<#= FullTableName  #>_AuditTrail_Delete') IS NOT NULL
	DROP TRIGGER <#= FullTableName #>_AuditTrail_Delete
GO
IF OBJECT_ID('<#= FullAuditTableName #>') IS NOT NULL
	DROP TABLE <#= FullAuditTableName #>	
GO

CREATE TABLE <#= FullAuditTableName #>
(
    [_ChangeId] int IDENTITY (1,1) PRIMARY KEY,
	[_OriginalLogin] sysname,
    [_UserId] int,
	[_ChangeDate] datetimeoffset NOT NULL,
	[_ChangeType] char(1) NOT NULL,
<#+ 
    DataRowCollection rows = schema.Rows;
    for(int rowIndex = 0; rowIndex < rows.Count; rowIndex++ )
    {
        DataRow row = rows[rowIndex];
        string fieldName = (string) row["ColumnName"];
        string dataTypeName= GetCondensedTypeName(row);
        if (dataTypeName != "text" && dataTypeName != "ntext" && dataTypeName != "image")
        {
            sb.Append(", [").Append(fieldName).Append("]");
            if (object.Equals(row["IsKey"], true)) sbpk.Append(", [").Append(fieldName).Append("]");
#>
    [<#=row["ColumnName"] #>] <#=dataTypeName #> <#= object.Equals(row["IsKey"], true) ? "NOT NULL": "NULL" #> <#= rowIndex < rows.Count - 1 ? "," : string.Empty #>
<#+
        }
    }
    string fieldNameCommaSeparatedList= sb.ToString();
    string pkFieldNameCommaSeparatedList= sbpk.ToString();
#>
)
GO

CREATE TRIGGER <#= FullTableName #>_AuditTrail_Insert
ON  <#= FullTableName #> AFTER INSERT
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO <#= FullAuditTableName #>([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType]<#= pkFieldNameCommaSeparatedList #>)
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'I'<#= pkFieldNameCommaSeparatedList #>
    FROM inserted
GO

CREATE TRIGGER <#= FullTableName #>_AuditTrail_Update
ON  <#= FullTableName #> AFTER UPDATE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO <#= FullAuditTableName #>([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType]<#= fieldNameCommaSeparatedList #>)
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'U'<#= fieldNameCommaSeparatedList #>
    FROM deleted
GO

CREATE TRIGGER <#= FullTableName #>_AuditTrail_Delete
ON  <#= FullTableName #> AFTER DELETE
AS
    IF @@ROWCOUNT = 0 RETURN;
    SET NOCOUNT ON;
    DECLARE @UserId int;
    SET @UserId = CONVERT(int, SUBSTRING(CONTEXT_INFO(), 1, 4));
    INSERT INTO <#= FullAuditTableName #>([_OriginalLogin], [_UserId], [_ChangeDate], [_ChangeType]<#= fieldNameCommaSeparatedList #>)
    SELECT ORIGINAL_LOGIN(), @UserId, SYSDATETIMEOFFSET(), 'D'<#= fieldNameCommaSeparatedList #>
    FROM deleted
GO
<#+
}
private static string[] nonSizedTypes = { 
    "bit", "tinyint", "smallint", "int", "bigint", "real", "float", "money", "smallmoney", "text", "ntext", "sql_variant",
    "image", "smalldatetime", "datetime", "date", "xml", "hierarchyid", "geography", "geometry", "uniqueidentifier"
    };
public string GetCondensedTypeName(DataRow row)
{
    string dataTypeName = (string) row["DataTypeName"];
    if (dataTypeName.EndsWith("hierarchyid")) dataTypeName = "hierarchyid";
    if (dataTypeName.EndsWith("geography")) dataTypeName = "geography";
    if (dataTypeName.EndsWith("geometry")) dataTypeName = "geometry";
    if (Array.IndexOf(nonSizedTypes, dataTypeName) >= 0) return dataTypeName;
    if (dataTypeName == "numeric" || dataTypeName == "decimal")
    {
        dataTypeName = "numeric(" + row["NumericPrecision"].ToString() + ", " + row["NumericScale"].ToString() + ")";
    }
    else if ((Type) row["DataType"] == typeof(string) || dataTypeName == "varbinary" || dataTypeName == "binary")
    {
        if ( (int) row["ColumnSize"] == 2147483647)
        {
            dataTypeName = dataTypeName + "(max)";
        }
        else
        {
            dataTypeName = dataTypeName + "(" + row["ColumnSize"].ToString() + ")";
        }
    }
    else if (dataTypeName == "timestamp" || dataTypeName == "rowversion")
    {
        dataTypeName = "binary(8)";
    }
    else if (dataTypeName == "datetime2" || dataTypeName == "datetimeoffset" || dataTypeName == "time")
    {
        dataTypeName = dataTypeName + "(" + row["NumericScale"].ToString() + ")";
    }
    return dataTypeName;
}
#>

Observa que en el método Render está la cadena de conexión. Modifícala para que apunte a la base de datos en cuestión. Observa también que en AuditTrail.ttinclude hay una instrucción USE App, cámbiala para la base de datos que estés utilizando

Luego para cada tabla que queramos auditar añadimos un nuevo T4 Template (Botón derecho del ratón sobre el proyecto -> «Add» -> «new Ítem» -> «Text Template») con el nombre de la tabla, por ejemplo «Contacts.tt» y un contenido como el siguiente:

<#@ template language="C#" hostspecific="True" debug="True" #>
<#@ output extension="sql" #>
<#@ include file="AuditTrail.ttinclude" #>
<#
    this.FullTableName = "dbo.Contacts";
    this.FullAuditTableName = "dbo.ContactsAudit";
    Render();
#>

 Cuando guardes el archivo se generará un archivo Contacts.sql que «cuelga» de «Contacts.tt». Puedes abrirlo y ejecutar el script desde el propio Visual Studio.