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.

Pero navegando 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 este blog.

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 periodo especifico.
  • 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.

    USE MYDATABASE–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),

    NewValue varchar(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 pk.TABLE_NAME = @TableName

    andCONSTRAINT_TYPE = ”PRIMARY KEY”

    andc.TABLE_NAME = pk.TABLE_NAME

    andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

     

    select @PKValueSelect = coalesce(@PKValueSelect+”+”,””) + ”convert(varchar(100), coalesce(i.” + COLUMN_NAME + ”,d.” + COLUMN_NAME + ”))”

    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,   

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c  

    where  pk.TABLE_NAME = @TableName  

    and CONSTRAINT_TYPE = ”PRIMARY KEY”  

    and c.TABLE_NAME = pk.TABLE_NAME  

    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

     

    if @PKCols is null

    begin

    raiserror(”no PK on table %s”, 16, -1, @TableName)

    return

    end

     

    select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

    while @field < @maxfield

    begin

    select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

    select @bit = (@field – 1 )% 8 + 1

    select @bit = power(2,@bit – 1)

    select @char = ((@field – 1) / 8) + 1

    if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (”I”,”D”)

    begin

    select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

    select @sql = ”insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)”

    select @sql = @sql + ” select ””” + @Type + ””””

    select @sql = @sql + ”,””” + @TableName + ””””

    select @sql = @sql + ”,” + @PKFieldSelect

    select @sql = @sql + ”,” + @PKValueSelect

    select @sql = @sql + ”,””” + @fieldname + ””””

    select @sql = @sql + ”,convert(varchar(1000),d.” + @fieldname + ”)”

    select @sql = @sql + ”,convert(varchar(1000),i.” + @fieldname + ”)”

    select @sql = @sql + ”,””” + @UpdateDate + ””””

    select @sql = @sql + ”,””” + @UserName + ””””

    select @sql = @sql + ” from #ins i full outer join #del d”

    select @sql = @sql + @PKCols

    select @sql = @sql + ” where i.” + @fieldname + ” <> d.” + @fieldname

    select @sql = @sql + ” or (i.” + @fieldname + ” is null and  d.” + @fieldname + ” is not null)”

    select @sql = @sql + ” or (i.” + @fieldname + ” is not null and  d.” + @fieldname + ” is null)”

    exec (@sql)

    end

    end

    SELECT @sql

    EXEC(@sql)

    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

    WHERE TABLE_NAME> @TABLE_NAME

    AND TABLE_TYPE= ‘BASE TABLE’

    AND TABLE_NAME!= ‘sysdiagrams’

    AND TABLE_NAME!= ‘Audit’

    END

     

    Para mas informacion:

  • Jon Galloway
    SQL Server 2005’s DDL triggers
    Nigel Rivett’s SQL Server Auditing triggers

    Espero que les sea de utilidad!!!

    37 comentarios en “Agregar un simple Trigger para auditar tu base de datos SQL Server”

    1. El problema de este tipo de auditorias es que no te dice quien ha realizado tal acción. Si tuvieses SingleSOn en la aplicación, aun se podría auditar de esta manera, pero si no, imposible. Habria que modificar las aplicaciones que modifican la base de datos con algo montado sobre Entreprise Library o Log4NET.

    2. Esto esta muy bueno muchas gracias!!,
      en cuanto al nombre de usuario @UserName dara como resultado el “nombre de la maquinanombre de la cuenta windows” en el caso de que se use autenticacion de windows para acceder a la base de datos, pero si se usa la autenticacion de sql server entonces la variable @UserName contedrá el nombre de usuario, por lo que se puede saber que usuario hizo que cambio. Muy bueno!!!

    3. Yo manejo un esquema similar a este solo que con plantillas para generar los triggers, el caso es que siempre me encuentro con la dificultad de conocer el nombre de la transaccion o el nombre del SP que realizó el insert y por lo tanto que desencadenó el trigger.

      hace rato que busco una solucion a este tema. te agradeceria si puedes ayudarme con eso

    4. Hola:
      el aporte esta muy bueno.
      solamente una observacion, cuando en el codigo se crea la variable @sql encontre en SQL 2000 que si esta concatenacion tiene un largo de mas de 4000 da error ( y el error que da es totalmente desorientate porque da error como si estuviera algo mas escrito en el codigo).
      la solucion fue dividir el codigo en dos

      @sql = “select campos… bla bla bla”
      @sql = @sql + “aca sigue el codigo”

      Espero que le ahorre a alguien el dolor de cabeza que me dio esto a mi ayer jajaja.
      saludos

    5. Que tal,
      Estoy probando el codigo, e hice varios cambios porque salian errores. El problema que tengo es que me sale un error al correrlo

      Servidor: mensaje 311, nivel 16, estado 1, procedimiento ANULACION_ORDEN_COMPRA_ChangeTracking, línea 35
      No se pueden usar columnas de tipo text, ntext o image en las tablas insertadas y eliminadas.

      Tengo varias tablas con campos de tipo TEXT, como podria solucionar esto ??

      Gracias de antemano.
      John

    6. @Carlos: Se puede hacer algun apaño 😉
      Solo necesitas modificar la aplicación para que le pase a un proceso almacenado el “usuario”. No el usuario de BBDD sino el usuario de la aplicación que se logeará contra una tabla de la BBDD.

      Una vez le pasas ese usuario, lo guardas en el context_info de la sesion, y añades una columna a la tabla de esta aplicación que tenga el valor por defecto context_info.

      El context_info pertenece a cada sesión, lo puedes leer de la vista de compatibilidad master.dbo.sysprocesses y se borra cuando el usuario abandona la sesion.

    7. saludos

      este trigger es para auditar tablas y quisiera felicitar al creador y a todos los que dan sus opiniones. mi enterrogante es como auditar la creacion de procedimientos almacenados.
      Tengo una aplicacion en la cual cada usuario tiene un procedimiento almacenado que engloba todos sus derechos sobre la aplicacion. mi intencion es lograr llenar una tabla con los procedimientos almacenados disponibles para asignar a los usuarios, de esta manera los administradores del sistema (que no son informaticos), ya tendrian en una tabla cuales procedimientos se pueden asignar.

      disculpen tanto blablabla.

    8. Msg 156, Level 15, State 1, Line 6
      Incorrect syntax near the keyword ‘SET’.
      Msg 137, Level 15, State 1, Line 11
      Must declare the variable ‘@TABLE_NAME’.
      Msg 137, Level 15, State 2, Line 17
      Must declare the variable ‘@TABLE_NAMEIS’.
      Msg 137, Level 15, State 2, Line 19
      Must declare the variable ‘@TABLE_NAME’.
      Msg 137, Level 15, State 2, Line 22
      Must declare the variable ‘@TABLE_NAME’.
      Msg 137, Level 15, State 1, Line 117
      Must declare the variable ‘@TABLE_NAME’.

    9. Encontre un problema que cuando la clave principal es compuesta, por ejemplo id1+id2+id3, sale todo concatenado el nombre y luego los valos de dichos campos, estoy en busca de una solucion para separar los nombres de campos y valores por “;”

    10. Revizando el script me econtre con los siguientes errores
      1. UserNamevarchar(128) es decir el tipo de dato esta pegado al nombre debe quedar asio UserName varchar(128).
      2. WHILE @TABLE_NAMEIS NOT NULL, la palabra IS esta pegada a la variable @TABLE_NAME debe quedar asip WHILE @TABLE_NAME IS NOT NULL

      Segui revizando y me encontre con este error lo cual no tengo idea que puede ser. Alguien que me colabore gracias.

      19:45:56 [USE – 0 row(s), 0.000 secs] Command processed. No rows were affected
      Code: 5701 SQL State: 01000 — Cambiado el contexto de base de datos a ‘bombas’.

      19:45:56 [IF – 0 row(s), 0.000 secs] Command processed. No rows were affected

      19:45:56 [DECLARE – 0 row(s), 0.000 secs] [Error Code: 170, SQL State: 37000] Línea 14: sintaxis incorrecta cerca de ‘=’.
      … 3 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 2 warnings, 1 errors]

    11. Server: Msg 173, Level 15, State 1, Line 28
      The definition for column ‘UserNamevarchar’ must include a data type.
      Server: Msg 156, Level 15, State 1, Line 6
      Incorrect syntax near the keyword ‘SET’.
      Server: Msg 137, Level 15, State 1, Line 12
      Must declare the variable ‘@TABLE_NAME’.
      Server: Msg 137, Level 15, State 1, Line 24
      Must declare the variable ‘@TABLE_NAMEIS’.
      Server: Msg 137, Level 15, State 1, Line 28
      Must declare the variable ‘@TABLE_NAME’.
      Server: Msg 137, Level 15, State 1, Line 34
      Must declare the variable ‘@TABLE_NAME’.
      Server: Msg 137, Level 15, State 1, Line 232
      Must declare the variable ‘@TABLE_NAME’.

    12. Corrijo algunos errores tipográficos del ejemplo:

      USE ¡¡¡¡MYDATABASE!!!! –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),

      NewValue varchar(1000),

      UpdateDate datetime DEFAULT (GetDate()),

      UserName varchar(128)

      )

      GO

      DECLARE @sql varchar(8000), @TABLE_NAME sysname

      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_NAME IS 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

      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

      INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

      where pk.TABLE_NAME = @TableName

      and CONSTRAINT_TYPE = ”PRIMARY KEY”

      and c.TABLE_NAME = pk.TABLE_NAME

      and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

      — Get primary key fields select for insert

      select @PKFieldSelect = coalesce(@PKFieldSelect+”+”,””) + ”””” + COLUMN_NAME + ””””

      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

      INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

      where pk.TABLE_NAME = @TableName

      and CONSTRAINT_TYPE = ”PRIMARY KEY”

      and c.TABLE_NAME = pk.TABLE_NAME

      and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

      select @PKValueSelect = coalesce(@PKValueSelect+”+”,””) + ”convert(varchar(100), coalesce(i.” + COLUMN_NAME + ”,d.” + COLUMN_NAME + ”))”

      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

      INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

      where pk.TABLE_NAME = @TableName

      and CONSTRAINT_TYPE = ”PRIMARY KEY”

      and c.TABLE_NAME = pk.TABLE_NAME

      and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

      if @PKCols is null

      begin

      raiserror(”no PK on table %s”, 16, -1, @TableName)

      return

      end

      select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

      while @field < @maxfield begin select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

      select @bit = (@field – 1 )% 8 + 1

      select @bit = power(2,@bit – 1)

      select @char = ((@field – 1) / 8) + 1

      if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (”I”,”D”)

      begin

      select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

      select @sql = ”insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)”

      select @sql = @sql + ” select ””” + @Type + ””””

      select @sql = @sql + ”,””” + @TableName + ””””

      select @sql = @sql + ”,” + @PKFieldSelect

      select @sql = @sql + ”,” + @PKValueSelect

      select @sql = @sql + ”,””” + @fieldname + ””””

      select @sql = @sql + ”,convert(varchar(1000),d.” + @fieldname + ”)”

      select @sql = @sql + ”,convert(varchar(1000),i.” + @fieldname + ”)”

      select @sql = @sql + ”,””” + @UpdateDate + ””””

      select @sql = @sql + ”,””” + @UserName + ””””

      select @sql = @sql + ” from #ins i full outer join #del d”

      select @sql = @sql + @PKCols

      select @sql = @sql + ” where i.” + @fieldname + ” <> d.” + @fieldname

      select @sql = @sql + ” or (i.” + @fieldname + ” is null and d.” + @fieldname + ” is not null)”

      select @sql = @sql + ” or (i.” + @fieldname + ” is not null and d.” + @fieldname + ” is null)”

      exec (@sql)

      end

      end

      SELECT @sql

      EXEC(@sql)

      SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

      WHERE TABLE_NAME> @TABLE_NAME

      AND TABLE_TYPE= ‘BASE TABLE’

      AND TABLE_NAME!= ‘sysdiagrams’

      AND TABLE_NAME!= ‘Audit’

      END

    13. Ejecuto todo el script y ok, pero en la ultima parte me da este error:
      Msg 208, Level 16, State 1, Line 6
      El nombre de objeto ‘INFORMATION_SCHEMA.Tables’ no es válido.

    14. Como hacer para correr el script en tabla que tiene campos text, ntext, o image..

      me sale el sgte error:

      Server: Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 59
      Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
      Server: Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 61
      Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.

    15. excelente!!! ESTE CODIGO ME CAYO ESPECTACULAR… no saben todo el trabajo que me ahorro.. y lo mejor es que es totalmente dinamico, no importa que luego agregue una nueva columna, ya automaticamente tambien la audita. genial.. genial.. genial.. !!!!

    16. me envia un mensaje de error y es el siguiente
      The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

      gracias espero puedan ayudarme

    17. gente muy bueno y anda muy bien. pero queria saber si se puede modificar para que tome un usaurio logueado en una aplicacion de vb.net. ees para un trabajo de lafacu, y necesito auditar el usuario logueado y no el de la maquina.
      Gracias; Muy buen trabajo con este SCRIPT

    18. gracias me ahorro mucho trabajo el cres es excelente tu aporte me gustaria saber si tengo dos base de datos uno llamado SAS y el otro Auditoria y quiero enviar a AUDITORIA LOS CAMBIOS EN LAS tablas de produccion SAS que vario en el script gracias por tu tiempo

    19. mejorando el script->
      – Corregidos los errores del script
      – agrupando algunas consultas para extraer datos:

      USE MYDATABASE–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),
      NewValue varchar(1000),
      UpdateDate datetime DEFAULT (GetDate()),
      UserName varchar(128)
      )
      GO

      DECLARE @sql varchar(8000), @TABLE_NAME sysname
      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_NAME IS 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,
      — Get primary key fields select for insert
      @PKFieldSelect = coalesce(@PKFieldSelect+”+”,””) + ”””” + COLUMN_NAME + ””””,
      @PKValueSelect = coalesce(@PKValueSelect+”+”,””) + ”convert(varchar(100), coalesce(i.” + COLUMN_NAME + ”,d.” + COLUMN_NAME + ”))”
      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      where pk.TABLE_NAME = @TableName
      and CONSTRAINT_TYPE = ”PRIMARY KEY”
      and c.TABLE_NAME = pk.TABLE_NAME
      and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

      if @PKCols is null
      begin
      raiserror(”no PK on table %s”, 16, -1, @TableName)
      return
      end

      select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
      group by ORDINAL_POSITION,COLUMN_NAME
      order by ORDINAL_POSITION desc
      while @field < @maxfield begin select @bit = (@field - 1 )% 8 + 1 select @bit = power(2,@bit - 1) select @char = ((@field - 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (”I”,”D”)
      begin

      select @sql = ”insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)”
      select @sql = @sql + ” select ””” + @Type + ””””
      select @sql = @sql + ”,””” + @TableName + ””””
      select @sql = @sql + ”,” + @PKFieldSelect
      select @sql = @sql + ”,” + @PKValueSelect
      select @sql = @sql + ”,””” + @fieldname + ””””
      select @sql = @sql + ”,convert(varchar(1000),d.” + @fieldname + ”)”
      select @sql = @sql + ”,convert(varchar(1000),i.” + @fieldname + ”)”
      select @sql = @sql + ”,””” + @UpdateDate + ””””
      select @sql = @sql + ”,””” + @UserName + ””””
      select @sql = @sql + ” from #ins i full outer join #del d”
      select @sql = @sql + @PKCols
      select @sql = @sql + ” where i.” + @fieldname + ” <> d.” + @fieldname
      select @sql = @sql + ” or (i.” + @fieldname + ” is null and d.” + @fieldname + ” is not null)”
      select @sql = @sql + ” or (i.” + @fieldname + ” is not null and d.” + @fieldname + ” is null)”
      exec (@sql)
      end
      end

      SELECT @sql
      EXEC(@sql)
      SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
      WHERE TABLE_NAME> @TABLE_NAME
      AND TABLE_TYPE= ‘BASE TABLE’
      AND TABLE_NAME!= ‘sysdiagrams’
      AND TABLE_NAME!= ‘Audit’
      END

    20. Este otro es para aquellos que necesitan que el registro de las auditorias este en una sola tupla. Es decir que todos los campos afectados por alguna de las operaciones (insert, update, delete) estarán concatenados y separados por ‘|’

      USE MYDATABASE–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(1000),
      OldValue varchar(2000),
      NewValue varchar(2000),
      UpdateDate datetime DEFAULT (GetDate()),
      UserName varchar(128)
      )
      GO

      DECLARE @sql varchar(8000), @TABLE_NAME sysname
      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_NAME IS NOT NULL
      BEGIN
      EXEC(‘IF OBJECT_ID (”TR_AUDIT_’ + @TABLE_NAME + ”’, ”TR”) IS NOT NULL DROP TRIGGER TR_AUDIT_’ + @TABLE_NAME+ ”)
      SELECT @sql =

      create trigger TR_AUDIT_’ + @TABLE_NAME + ‘ on ‘ + @TABLE_NAME+ ‘ for insert, update, delete
      as

      declare @bit int ,
      @field int ,
      @maxfield int ,
      @char int ,
      @fieldname varchar(64) ,
      @TableName varchar(128) ,
      @PKCols varchar(1000) ,
      @sql varchar(8000),
      @UpdateDate varchar(21) ,
      @UserName varchar(128) ,
      @Type char(1) ,
      @PKFieldSelect varchar(1000),
      @PKValueSelect varchar(1000),
      @fieldsNames varchar(1000) ,
      @ValuesOld varchar(2000),
      @ValuesNew varchar(2000)

      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,
      @PKFieldSelect = coalesce(@PKFieldSelect+”+””|””+”,””) + ”””” + COLUMN_NAME + ””””,
      @PKValueSelect = coalesce(@PKValueSelect+”+””|””+”,””) + ”convert(varchar(1000), coalesce(i.” + COLUMN_NAME + ”,d.” + COLUMN_NAME + ”))”
      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      where pk.TABLE_NAME = @TableName
      and CONSTRAINT_TYPE = ”PRIMARY KEY”
      and c.TABLE_NAME = pk.TABLE_NAME
      and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

      if @PKCols is null
      begin
      raiserror(”no PK on table %s”, 16, -1, @TableName)
      return
      end

      select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
      while @field < @maxfield begin select @field = min(ORDINAL_POSITION),@fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
      group by ORDINAL_POSITION,COLUMN_NAME
      order by ORDINAL_POSITION desc

      select @bit = (@field – 1 )% 8 + 1
      select @bit = power(2,@bit – 1)
      select @char = ((@field – 1) / 8) + 1
      if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (”I”,”D”)
      begin
      select
      @fieldsNames = coalesce(@fieldsNames +”+””|””+”,””, ””) + ”””” + COLUMN_NAME + ””””,
      @ValuesOld = coalesce(@ValuesOld +”+””|””+”,””, ””) + ”convert(varchar(2000), coalesce(d.” + c.COLUMN_NAME + ”,””””))”,
      @ValuesNew = coalesce(@ValuesNew +”+””|””+”,””, ””) + ”convert(varchar(2000), coalesce(i.” + c.COLUMN_NAME + ”,””””))”

      from
      INFORMATION_SCHEMA.COLUMNS c
      where c.TABLE_NAME = @TableName
      and c.COLUMN_NAME=@fieldname
      end
      end

      select @sql = ”insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)”
      select @sql = @sql + ” select ””” + @Type + ””””
      select @sql = @sql + ”,””” + @TableName + ””””
      select @sql = @sql + ”,” + @PKFieldSelect
      select @sql = @sql + ”,” + @PKValueSelect
      select @sql = @sql + ”,” + @fieldsNames
      select @sql = @sql + ”,” + @ValuesOld
      select @sql = @sql + ”,” + @ValuesNew
      select @sql = @sql + ”,””” + @UpdateDate + ””””
      select @sql = @sql + ”,””” + @UserName + ””””
      select @sql = @sql + ” from #ins i full outer join #del d ”
      select @sql = @sql + @PKCols

      exec (@sql)


      SELECT @sql
      EXEC(@sql)
      SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
      WHERE TABLE_NAME> @TABLE_NAME
      AND TABLE_TYPE= ‘BASE TABLE’
      AND TABLE_NAME!= ‘sysdiagrams’
      AND TABLE_NAME!= ‘Audit’
      END

    21. Excelente aporte, yo modifique un poco el trigger para que funcione para n capas, pero se basa en que cualquier tabla del sistema tenga un campo aud_usuario, para que este decienda desde la capa web hasta el trigger de la base de datos, En cuanto al ultimo post de Alexander Cuellar Jimenez en que dice para que salga concatenado me parece chevere, pero hay un detalle que modificar si actualizas tres campos se hace tres inserts en la base la tabla de audit, y bastaría con uno pues los campos y valores ya estan concatenados

    22. Hola buenas noches,

      Yo quisiera saber como puedo cambiar

      @UserName = system_user

      por el usuario que inicio sesion que no esta logueado a nivel de base de datos, sino que esta almacenado en una tabla usuario.

      Saludos.

    23. Hola Esteban,

      Soy nueva en estos medios y el concepto es excelente, pero, que puedo hacer si mis tablas no contienen llave primaria ?

      saludos!

    Deja un comentario

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *