Trigger genérico para auditar cambios en una tabla

El siguiente trigger permite auditar los cambios (Inserciones, Actualizaciones y eliminaciones) a una tabla. Es un trigger genérico. Es decir, el mismo trigger funciona para cualquier tabla de la base de datos. Hay que tener en cuenta solo tres cosas:

  1. Se debe crear una tabla de log para almacenar la información de auditoría.
  2. En el script hay que cambiar manualmente el nombre de la tabla sobre la cual se esta creando el trigger.
  3. Solo funciona con tablas que tienen llave primaria.

Este trigger fue diseñado por Nigel Rivett y funciona con las versiones 2005 en adelante. He hecho cambios mínimos de forma y he traducido e incluido comentarios para hacer mas legible y entendible el script.

Lo primero que hay que hacer es crear la tabla de auditoría, sobre la cual el trigger escribirá el detalle de las transacciones realizadas:

CREATETABLEdbo.logTransacciones(

  TipoTrnchar(1),

  Tablavarchar(128),

  PKvarchar(1000),

  Campovarchar(128),

  ValorOriginalvarchar(1000),

  ValorNuevovarchar(1000),

  FechaTrndatetime,

  Usuariovarchar(128))

GO

.

En esta tabla se almacena el tipo de transacción(I-Insert, U-Update, D-Delete), el nombre de la tabla, la llave del registro, el o los campos que han sufrido algún cambio, el valor original y el valor nuevo para las actualizaciones y por ultimo la fecha en la que se hizo el cambio y el usuario que lo ejecutó. Cualquier cambio realizado en cualquier tabla de la base de datos que tenga el trigger, será almacenado aquí

Vamos ahora a crear la tabla Cliente, que nos servirá para probar el trigger. Sobre esta tabla ejecutaremos el script para crearle un trigger y haremos los cambios para ver como se van grabando los datos en la tabla de auditoría:

CREATETABLEdbo.Cliente(

  IdClienteintIDENTITY(1,1)notnull,

  Nombresvarchar(100),

  Apellidosvarchar(100),

  TipoDocumentochar(3),

  NumeroDocumentovarchar(15))

GO

 

ALTERTABLEdbo.ClienteADDCONSTRAINTPK_ClientePRIMARYKEY (IdCliente)

GO

.

A continuación el script del trigger genérico. Tener mucha precaución de actualizar manualmente el nombre de la tabla al momento de almacenar el valor de la variable @TableName:

NOTA: para bajar el script del trigger hacer clic aquí

/*

————————————————————————-

PROPOSITO | Capturar los cambios realizados en la tabla.

————————————————————————-

NOTAS | -Solo crearlo en tablas donde realmente se necesita auditar

      | -La tabla que se desea auditar debe tener llave primaria

      | -Previamente se requiere crear la siguiente tabla:

      |   CREATE TABLE dbo.logTransacciones (

      |    TipoTrn char(1), Tabla varchar(128),

      |    PK varchar(1000), Campo varchar(128),

      |    ValorOriginal varchar(1000), ValorNuevo varchar(1000),

      |    FechaTrn datetime, Usuario varchar(128))

      | -Cambiar valor de @TableName para coincidir con tabla que se

      | desea auditar                    

————————————————————————-

PARAMETROS DE ENTRADA| NA

————————————————————————-

PARAMETROS DE SALIDA | NA

————————————————————————-

CREADO POR           | Nigel Rivett

FECHA CREACION       | ND           

————————————————————————-

HISTORIAL DE CAMBIOS | FECHA      RESPONSABLE       MOTIVO

                     | ———- —————– ———————

                     | 15/02/2010 Alberto De Rossi  -Cambio de querys a

                     |                               estandar ANSI.

                     |                              -Traducción de

                     |                               comentarios al

                     |                               castelllano

                     |                              -Arreglos de forma

————————————————————————-

*/

ALTERTRIGGERdbo.trIUDClienteONClienteFORINSERT,UPDATE,DELETE

AS

 

DECLARE@bitint,     

        @fieldint,   

        @maxfieldint,

        @charint,    

        @fieldnamevarchar(128), 

        @TableNamevarchar(128), 

        @PKColsvarchar(1000),   

        @sqlvarchar(2000),

        @UpdateDatevarchar(21), 

        @UserNamevarchar(128),  

        @Typechar(1),

        @PKSELECTvarchar(1000)

     

SELECT@TableName=‘Cliente’–<– cambiar el nombre de la tabla

 

— Fecha y Usuario

SELECT@UserName=system_user,

       @UpdateDate=convert(varchar(8),getdate(), 112)+

                     ‘ ‘+

                     convert(varchar(12),getdate(), 114)

 

SETNoCountON

 

–Identificar que evento se está ejecutando (Insert, Update o Delete)

–en base a cursores especiales (inserted y deleted)

ifexists(SELECT*FROMinserted)

  ifexists(SELECT*FROMdeleted)–Si es un update

    SELECT@Type=‘U’

  else                              –Si es un insert

    SELECT@Type=‘I’

else                                –si es un delete

    SELECT@Type=‘D’

     

— Obtenemos la lista de columnas de los cursores

SELECT*into#insfrominserted

SELECT*into#delfromdeleted

     

— Obtener las columnas de llave primaria

SELECT@PKCols=coalesce(@PKCols+‘ and’,‘ on’)+

       ‘ i.’+

       c.COLUMN_NAME+‘ = d.’+

       c.COLUMN_NAME

 FROMINFORMATION_SCHEMA.TABLE_CONSTRAINTSpk

  JOININFORMATION_SCHEMA.KEY_COLUMN_USAGEc

  ONc.TABLE_NAME=pk.TABLE_NAME

  ANDc.CONSTRAINT_NAME=pk.CONSTRAINT_NAME

 WHEREpk.TABLE_NAME=@TableNameAND

  pk.CONSTRAINT_TYPE=‘PRIMARY KEY’

     

— Obtener la llave primaria y columnas para la inserción en la tabla de auditoria

SELECT

 @PKSELECT=coalesce(@PKSelect+‘+’,)+

 ”'<‘+

 COLUMN_NAME+

 ‘=”+convert(varchar(100),coalesce(i.’+

 COLUMN_NAME+‘,d.’+

 COLUMN_NAME+‘))+”>”’

 FROMINFORMATION_SCHEMA.TABLE_CONSTRAINTSpk 

 JOININFORMATION_SCHEMA.KEY_COLUMN_USAGEc

  ONc.TABLE_NAME=pk.TABLE_NAME

  ANDc.CONSTRAINT_NAME=pk.CONSTRAINT_NAME

 WHEREpk.TABLE_NAME=@TableName

  ANDCONSTRAINT_TYPE=‘PRIMARY KEY’

     

if@PKColsisnull–<– Este trigger solo funciona si la tabla tiene llave primaria

 BEGIN

  RAISERROR(‘no PK on table %s’, 16,1,@TableName)

  RETURN

 END

     

–Loop para armar el query de inserción en la tabla de log.

–Un registro por cada campo afectado.

SELECT

 @field= 0,

 @maxfield=max(ORDINAL_POSITION)

 FROMINFORMATION_SCHEMA.COLUMNS

 WHERETABLE_NAME=@TableName

     

while@field<@maxfield

 BEGIN

  SELECT@field=min(ORDINAL_POSITION)

   FROMINFORMATION_SCHEMA.COLUMNS

   WHERETABLE_NAME=@TableNameandORDINAL_POSITION>@field

  SELECT@bit=(@field 1 )% 8 + 1

  SELECT@bit=power(2,@bit 1)

  SELECT@char=((@field 1)/ 8)+ 1

  ifsubstring(COLUMNS_UPDATED(),@char, 1)&@bit> 0

     or@Typein(‘I’,‘D’)

   BEGIN

     SELECT@fieldname=COLUMN_NAME

      FROMINFORMATION_SCHEMA.COLUMNS

        WHERETABLE_NAME=@TableNameandORDINAL_POSITION=@field

     SELECT@sql=‘insert LogTransacciones (TipoTrn, Tabla, PK, Campo, ValorOriginal, ValorNuevo, FechaTrn, Usuario)’

     SELECT@sql=@sql+    ‘ SELECT ”’+@Type+””

     SELECT@sql=@sql+    ‘,”’+@TableName+””

     SELECT@sql=@sql+    ‘,’+@PKSelect

     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

       

SETNoCountOFF

GO

.

Ahora vamos a probar el trigger. Vamos a insertar un registro en la tabla Cliente:

–Primera insersión

INSERTINTOCliente(Nombres,Apellidos,TipoDocumento,NumeroDocumento)

VALUES (‘Guillermo’,‘Morales Dueñas’,‘DNI’,‘03247159’)

GO

 

SELECT*FROMCLiente

GO

image

 

SELECT*FROMLogTransacciones

GO

image

.

Obsérvese que en la tabla de auditoría se han creado un registro por cada campo del registro que se ha insertado en la tabla Cliente. El tipo de transacción es I de Insert y el campo Valor Original es nulo por que este es un nuevo registro. Hagamos una segunda prueba.

–Segunda insersión

INSERTINTOCliente(Nombres,Apellidos,TipoDocumento,NumeroDocumento)

VALUES (‘Ana’,‘Sanchez Maldonado’,‘DNI’,‘18342711’)

GO

 

SELECT*FROMCLiente

GO

image

 

SELECT*FROMLogTransacciones

GO

image

.

Nuevamente en la tabla de auditoría se han creado un registro por cada campo del registro que se ha insertado en la tabla Cliente. Pero aquí se observa que es dificil identificar cuales de los registros de la tabla de auditoría pertence a cada transacción de inserción. La unica forma de identificarlo es con el campo PK, que indica la llave primaria del registro insertado, ya que la llave primaria es única. Quizá sería util añadir un campo con un número identificador de transacción.

Ahora vamos a probar la tabla con una actualización:

–Actualización de datos

UPDATEClienteSETNombres=‘Ana María’WhereIdCliente= 2

GO

 

SELECT*FROMCLiente

GO

image

 

SELECT*FROMLogTransacciones

GO

image

.

Obsérvese que solo se ha ingresado un solo registro en la tabla de auditoría, ya que solo se actualizó un campo de la tabla Cliente. El tipo de transacción es U de Updated y los campos ValorOriginal y ValorNuevo si tienen datos reflejando el antes y el despues de la actualización. Nuevamente mi observación de que los datos en esta tabla estan un poco mezclados, cuando hay muchos registros no debe ser tan facil ubicar cuales perteneces a una transacción en particular.

Por ultimo, probaremos con una eliminación:

–Eliminación de datos

DELETEClienteWhereIdCliente= 1

GO

 

SELECT*FROMCLiente

GO

image

 

SELECT*FROMLogTransacciones

GO

image

.

Aquí aparece un registro por cada campo del registro eliminado de la tabla Cliente. El tipo de transacción es D de Deleted y el campo ValorNuevo es nulo por que el registro se ha borrado, solo registra el valor original

CONCLUSIONES: Esta es una propuesta interesante y practica de Nigel Rivett para auditar una tabla en particular. Se debe tener en cuenta que no es necesario crear este trigger en todas las tablas de una base de datos, solo en aquellas a las que se desea auditar. Tener en cuenta tambien que la ejecución del trigger y el procesamiento de los datos de auditoría añade sobrecarga al procesador y a la memoria, lo cual puede hacer que el tiempo de respuesta de las transacciones disminuya. Se recomienda monitorear con Profiler para medir el impacto de implementar el trigger.

105 comentarios en “Trigger genérico para auditar cambios en una tabla

  • Que buen aporte, funcional y perfectamente explicado. No tienes idea de lo mucho que me has ayudado, mil gracias.
    Un saludo y las mejores vibras.

    • Laura: que bien que te haya servido. Te invitamos a que te suscribas para que estés al tanto de nuestras publicaciones.
      Saludos
      Alberto

  • Gracias por compartir esta información esta excelente … andaba buscando algo así.
    una consulta.
    como puedo registrar el nombre del usuario que hizo el cambio, me refiero al nombre de usuario de la aplicación no al nombre de usuario a la base de datos.
    tengo una bd con una tabla usuarios (nomUser, passUser …. etc)
    quiero registrar ese usuario.

    espero me puedan ayudar.
    SLDS

    • Hola Patricio: En SQL Server hay dos funciones del sistema para averiguar usuarios:
      USER_NAME() – nombre de usuario de base de datos
      SUSER_NAME() – nombre del login

      El tema es que, si necesitas saber el nombre del usuario de tu aplicación, ya no depende de SQL Server; depende de tu aplicación. Una forma es enviar como parámetro el código del usuario en las llamadas que hace tu aplicación a SQL Server.

      • y este parametro como es posible conectarlo a trigger, segun yo este triger se activara en el caso de; insert, update o delete a diferencia de un procedure, que se ejecuta durante la aplicacion y es posible enviar parametros, pero de requerir enviar este parametro hacia el trigger como seria?

        • Arturo, como tu bien dices, el trigger se dispara automáticamente luego de un evento (insert, update o delete) y efectivamente los triggers no reciben parámetros. En la solución que le proponía a Arturo, ya no se usaría un trigger si no un procedemiento almacenado que guarde la información de auditoría luego de terminar la transacción.

          Saludos

  • Hola,

    Muchas gracias por tu ejemplo, me sirvió de mucho… realmente excelente y muy bien explicado.

    Sin embargo, al cargar el triger en mi tabla e intentar actualizar o insertar, obtengo un error en mi aplicacion q dice: “Sintaxis incorrecta cerca de la palabra clave and”…

    tienes idea de porq podría ser???

    mil gracias por toda tu ayuda ^^

    saludos.

    • Guido, que versión y edición de de SQL Server estas utilizando?
      Cómo puedes ver, en el trigger hay varios AND. Quizá puedas verificar si mensaje de error devuelve el número de línea del programa.

      Otra cosa que te recomiendo que hagas es que bajes el archivo de texto con el script del trigger, que está mas arriba en el artículo.

  • Gracias por tu pronta respuesta ^^

    Pues te comento,

    utilizo sql server 2005 standar, curiosamente lo solucion´r, pero no se si de manera correcta o cuales sean las consecuencias :P

    en esta linea:

    SELECT @PKCols = coalesce(@PKCols + ‘and’,’ on ‘) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME

    le quite el “and”, asi:

    SELECT @PKCols = coalesce(@PKCols,’ on ‘) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME

    y dejó de aparecerme el error…

    El vb6 no devuelve la linea del error, y, sí, si me bajé el documento del enlace :D…

    Como crees q me puede afectar el quitar dicho and???

    gracias y saludos…

    • Guido, si creas el trigger en una tabla con llave primaria compuestas (más de una columna), el trigger va a fallar. Mientras la llave primaria de la tabla tenga una sola columna, no vas atener problemas.

      Saludos y suerte

  • Buenas, perdon por demorar tanto en responder, pues la tabla en la cual puse el trigger tiene una sola columna como clave primaria.

    Sin embargo, luego del cambio que le hice al trigger ha funcionado correctamente, o por lo menos no me ha dado problemas.

    Gracias mil por tu tiempo :D

    saludos…

  • Hola,

    Nuevamente yo molestando por aqui :D

    Ya se como afecta quitar ese
    + ' and '

    Cuando la cadena esta completa como en el ejemplo:
    SELECT @PKCols = coalesce(@PKCols + ‘and’,’ on ‘) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME

    La sentencia busca todas las claves primarias de la tabla y las concatena para insertarlas en la tabla de auditoria…

    Si la sentencia estuviese asi:
    SELECT @PKCols = coalesce(@PKCols,’ on ‘) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME

    Interpretará q solo existe una columna como clave primaria, y de existir clave primaria compuesta lanzará error.

    Pero como digo este es mi caso particular, puede q a otras personas no les funcione de igual manera, solo les pongo mi caso por si alguien se preguntaba lo mismo :D

    saludos…

    • Es correcto lo que mencionas Guido: Esas líneas obtienen la llave primaria de la tabla y controlan si la llave es compuestas (más de una columna). Mientras tengas llaves de una sola columna no vas a tener problemas.

      Gracias por tu aporte! y recuerda visitar los foros, ya sea para preguntar o responder.

      Saludos

  • Hola, intente implementar el trigger, pero justamente la tabla que deseo auditar tiene una clave primaria compuesta, que se puede hacer en ese caso?

    • Serguio, habría que reescribir el trigger para controlar la llave compuesta. Que versión de SQL Server usas? A partir de la versión 2008 existe una herramienta llamada Change Data Capture (CDC), que permite capturar los cambios a una tabla, muy parecido a lo que hace este trigger pero sin necesidad de programar. Ponerlo en marcha es simple. Revisa este link:

      http://msdn.microsoft.com/es-pe/library/bb522489(v=sql.100).aspx

    • Karina, lamentablemente este trigger solo funciona si la tabla tiene llave primaria. Si tu versión de SQL Server es 2008 o mayor, puedes probar con Change Data Capture. Es una alternativa para rastrear cambios sin necesidad de que la tabla tenga PK.

      Saludos

  • Que tal, muy bueno tu script, una consulta: Debido a la complejidad del script y a la cantidad de lineas, como se comporta la db al usar tu script, teniendo en cuenta si por ejemplo son 5 las terminales que lo usan al mismo tiempo? la velocidad de ejecucion sigue siendo la misma? o con cuantas terminales usando al mismo tiempo se empieza a notar cambios en la velocidad de ejecucion del script?

    • Hola Rodrigo, antes que nada, disculpa por la demora en responder.
      Cuando implementas un trigger, el tema de velocidad de respuesta siempre es un tema a considerar. Las velocidades de respuesta están en función a muchas variables: Capacidad del servidor de base de datos, capacidad de la red, capacidad de los terminales, eficiencia del programa, diseño de la base de datos, etc.
      Este trigger, aunque tiene muchas líneas, no impacta considerablemente en el tiempo de respuesta de las transacciones. Como siempre, recomiendo que antes de implementarlo lo sometas a pruebas, utilizando herramientas de monitoreo como pueden ser el SQL Profiler de SQL Server o el Monitor de Desempeño de Windows Server.

  • Buenas tardes, gracias por el aporte, pero tengo un inconveniente al actualizar un dato de tipo entero, el trigger no registra la actualizacion en la tabla de auditoria.

    • Hola Ilmer. El trigger esta diseñado para registrar los cambios, sin importar el tipo de dato. Podrías ofrecerme más informaicón al respecto? Quizá puedas pasarme los scripts de las tablas involucradas para hacer una prueba.

      Saludos

  • Hola,
    al momento de ejecutar el trigger me sale error, que es:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ON documento FOR INSERT, UPDATE, DELETE AS DECLARE @bit int , @field int , ‘ at line 1

    estoy realizando el trigger en PHPMyAdmin – XAMPP

    • Hola Víctor, el tema está en que este trigger está diseñad para SQL Server. No te puedo garantizar su funcionamiento en MYSQL. Hay una persona que está probando “traducirlo” a MySQL. Le he pedido que nos cuente como le fue.

      Saludos

  • HOla…Es muy interesante y muy claro el trigger… Me sirve para lo que estoy haciendo.. pero mi problema es que en vez de utilizar un while (while@field<@maxfield) para recorrer las posiciones deberia hacerlo con un cursor sobre una imagen guardada de la tabla que quiero auditar, en este caso la tabla CLiente que utilizaste… Estoy dando vueltas como agregarle eso a tu trigger de ejmeplo.. Como podria hacerlo??

    • Hola Eva, La utilización de un cursos puede ser un buen aporte para una nueva versión del trigger, vamos a tenerlo en cuenta.

      Saludos

  • Encontre lo que necesitaba. Ahora solamente me falta echarlo a andar pero en MySQL (donde no hay mayor variedad). Te felicito y agradezco. Un fuerte abrazo.

    • Oscar, no debería haber límite, ya que la auditoría graba cada columna como un registro, por lo tanto es ilimitado.

  • Excelente aporte, mis felicitaciones, solo quisiera saber como agregarias un id de transaccion como indicabas. GRACIAS

  • hola una pequeña pregunta es que yo deseo que en el campo TipoTrn no aparezca solo la inciacial de la transaccion si no el nombre completo ya sea INSERCION MODIFICACIO Y ELMINACION para que el usuario entienda que fue lo que paso, es que no hallo como hacer esto gracias

    • Hola Viviana, tienes que hacer lo siguiente:

      1. Cuando crees la tabla LogTransacciones, el campo TipoTrn debe ser un varchar (15)
      2. En el trigger, la variable @Type tambien tiene que ser varchar(15)
      3. En el trigger, cuando se identifica el evento y se le asigna el valor a la variable @Type, en vez de la inicial indicas la palabra completa. Y listo!

      Suerte

  • Buenas noches es muy interesante el script alguien ya lo ha podido traducir para mysql?  hace tiempo estuve buscando algo parecido para mysql gracias

     

    • Hola Ricardo: Por ahora nos estamos concentrando en SQL Server, pero vamos a buscar algún especialista en MySQL que pueda ayudarnos. Si tenemos novedades las publicaremos

      Suerte

  • Amigo una duda, si cambio el schema de la tabla donde registro los log y modifico la parte del codigo del trigger donde hace el insert a la tabla logTransacciones agregandole el schema correspondiente, sql me arroja un error de que no es posible encontrar el objeto con el nombre .logTransacciones, cuando se gatilla el trigger
    A que se debe esto ??

     

    saludos y muy buen aporte,

    Gracias

    • Luciano, puedes incluir la función HOST_NAME() para obtener el nombre de la máquina desde donde se ejecutó el cambio

      Saludos

  • Buenos dias profesor. En mi caso es sobre la duda de casi todos, los trigger solo registran eventos. en este caso insert, update y deleted, yo necesito saber que usuario hizo alguno de esos eventos. osea el que este logueado.. si podría adjuntar algún ejemplo para solucionar.

    • Hola José. Incluir al usuario va a depender de la arquitectura de tu aplicación.

      -Si usas autenticación de confianza Windows puedes obtener el nombre del usuario con la función SUSER_NAME()
      -Si usas autenticación de SQL con usuario nombrado puedes obtener el nombre del usuario con la función USER_NAME()
      -El problema está cuando usas autenticación de SQL con usuario gen genérico es muy difícil obtener el nombre de usuario

      Ahora, si el sistema es cliente servidor, puedes obtener también el nombre de la PC del usuario con la función HOST_NAME()

      Si deseas envíame más detalles a mi correo alberto.derossi@dblearner.com

      Saludos

  • Buenas tardes,

    Yo tengo el mismo problema reportado

    al cargar el triger en mi tabla e intentar actualizar o insertar, obtengo un error en mi aplicacion q dice: “Sintaxis incorrecta cerca de la palabra clave and”…

    La instrucción que se esta ejecutando  es:

    SELECT @PKCols = coalesce(@PKCols + ‘and’,’ on ‘) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME

    Mis tablas tienen llaves compuestas.

    Cuando ejecuto el query directamente en sql no me genera error, y me actualiza la tabla de auditoria pero si lo hago a través de la aplicación saca el error

    la aplicación esta desarrollada en Power Builder 9

    El sql que tengo es 2008 r2.

    Gracias

     

     

      • Hola Alberto , tengo el mismo problema que esta publicacion, tuvieron existo en la solucion? como puedo solucionarlo?, ejecutando el query directamente en sql realiza bien el trigger pero desde mi aplicacion en este caso tambien powerbuilder10 me marca el mismo error que menciona, cabe mencionar que tambien lo utilizara desde una aplicacion en VB, te comento que mi llave tambien es compuesta y el sql 2000.

        agradeciendo de antemano la respuesta.

  • buenas noches a todos,

    Tengo un problema con el trigger, tengo una tabla que tiene un tipo de dato xml, entonces a la hora que hace el recorrido ese tipo no pasa, alguna sugerencia de como resolver ese caso

     

    Gracias por todo

  • Hola, excelente articulo.

     

    Quiero ponerlo a prueba. Hasta ahora el cliente SQL me lanza este error :

     

    [Err] 42000 – [SQL Server]Incorrect syntax near the keyword ‘END’.

     

    He bajado el codigo desde el link que colocaste, realmente no entiendo cual puede ser el error.

  • Hola, necesito de su ayuda para auditar ciertas tablas a mi base de datos, se creo el trigger, funciona correctament, pero cuando creo un usuario nuevo y al hacer modificaciones o inserciones en tablas auditadas, me sale error de que no tengo permisos a la tabla donde se guardan los datos del usuario quien mofico asi como que dato modifico, alguien sabe como dar por default los permios a las tablas que funcionan como bitacora?.

    • Hola Javier, la manera más rápida de asignar permisos por defectos es modificando el rol Public. Este rol se asigna por defecto a todos los usuarios cuando se crean. Puedes modificar el rol Public asignando permiso de lectura y escritura a la tabla de auditoría

      Si no quieres modificar el rol public debes crear un rol nuevo con permisos para las tablas en cuestión y al momento de crear los usuarios debes hacerlos miembros de dicho rol

      Saludos

      Alberto

    • Hola Daniela: Pero aparece algún mensaje de error? Cualquier información adicional para detectar el problema es bienvenida (Versión y edición de SQl Server por ejemplo)

  • Estimado tengo problemas con algunos campos de mi tabla a auditar por ejemplo [User] o [Plan], que modificación tendría que realizar en el trigger para que funcione correctamente.

  • Buen día Alberto De Rossi fíjate que a mi me funciona bien el trigger pero al momento de que actualizo un campo el trigger no se dispara únicamente lo hace cuando inserto. Sabes cual podría ser el problema?

    • Hola! disculpa la demora en responder. El trigger debe disparar cuando hace insert, update o delete. No hemos tenido problema con eso. Fíjate bien en la línea siguiente del CREATE TRIGGER que aparezca la sentencia FOR INSERT,UPDATE,DELETE

      Saludos

      Alberto

  • Hola tengo un problema a la hora de compilar.

     

    Msg 311, Level 16, State 1, Procedure Seguridad, Line 37
    Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
    Msg 311, Level 16, State 1, Procedure Seguridad, Line 38
    Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.

    • Hola, hay ciertos tipos de datos que no se pueden usar. Los tipos de datos text, ntext o image ya no deben ser utilizados en implementaciones de SQL Server. Se mantienen solo por campatibilidad con versiones anteriores. En su reemplazo de deben usar varchar(max) o vatbinary(max)

      Suerte

  • Hola tengo un problema me sale un error con las llave primaria ‘no PK on table ‘mi tabla”, no entiendo porque, ya revise que la tabla a auditar tenga llave primaria pero sigue saliendome ese error,no se si podrias ayudarme… esta es la estructura de mi tabla:

    CREATE TABLE ‘Mi_tabla'(
    [fi_IdIWAutomatica] [int] IDENTITY(1,1) NOT NULL,
    [fc_HoraInicial] [time](7) NOT NULL,
    [fc_Horafinal] [time](7) NOT NULL,
    [fc_HoraEjecucion] [time](7) NOT NULL,
    [fb_Estatus] [bit] NULL,
    CONSTRAINT [PK_tbl_OpeIWAutomatica] PRIMARY KEY CLUSTERED
    (
    [fi_IdIWAutomatica] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Saludos y espero puedas apoyarme.

    • Hola, acabo de probar con el script que has pasado y ha funcionado bien. Que versión y edición de SQL Server estas usando?
      Asegúrate de que en el script decreación del trigger este haciendo referencia a la tabla correcta

      Saludos y suerte

    • Ernesto, este artículo tiene tiempo funcionando y es uno delos más apreciados por la comunidad. Son muchos colegas que lo han implementado. Me puedes comentar cuáles son los errores?

  • Hola a todos. Excelente artículo y muy útil. Me ha servido muy bien aunque tengo un problema. Una de las tablas que necesito auditar tiene un campo imagen que SQL no me deja referenciar. No puedo modificar la estructura de la BD ya que no es mia.

    Las líneas SELECT * into #ins from inserted y SELECT * into #del from deleted generan error en este tipo de tablas. He intentado armar la consulta dinámica pero las tablas inserted y deleted no quedan accesibles. Alguna idea cómo solucionar esto?

    Sobre el tema del uso obligatorio de una llave primaria pues es posible realizar algunas modificaciones para lograr lo siguiente:

    1. Si hay PK entonces utilizarla
    2. Si no hay PK evaluar si hay campo identity y utilizarlo
    3. Si no hay PK ni identity entonces utilizar todos los campos para lograr un identificador único.

    Si es posible en cuanto regrese a mi trabajo (estoy de vacaciones) puedo compartir la modificación para los casos anterior.

    Gracias a todos por compartir

    • Gracias Ariel. Te animo a que compartas los ajustes que hagas. Sobre el campo Image, efectivamente hay restricciones. Ahora se recomienda usar varbinary(max)en vez del tipo de dato IMAGE. Saludos

  • Hola que tal?

    Tengo 2 preguntas:

    En la parte donde indicas que debemos cambiar el nombre de la tabla, es obviamente el nombre de la tabla a auditar, pero estoy haciendo pruebas con el código tal y cual lo has publicado (solo para probar) pero igual me sale error en dicha línea.
    En el IDCliente tengo un problema ya que al momento de insertar registros se ingresa un número predeterminado, pero al momento de eliminarlos todos, en lugar de reiniciarse el conteo, los registros que inserto cuentan con el ID 10 u 11, en lugar de 1 y 2.

    A espera de tu pronta respuesta.

     

     

    • Hola. En efecto el nombre de la tabla se refiere a la tabla a auditar, la misma a la que se le está colocando el trigger. Si ves mi último comentario puedes sustituir ese código de forma tal que esta variable se llena sola.

      Lo del IDCliente no lo entendí bien. En general no deberías tener problemas sea cual sea el valor ya que la filosofía es ir guardando los valores sin importar el tipo o valor en si. Si es posible podrías argumentar un poquito mas ese caso.

      Saludos

  • Hola a todos. Basado en lo que había comentado anteriormente acá comparto este procedimiento. Utiliza la misma filosofía del trigger y devuelve para un nombre de tabla la combinación que hace un registro único. Primero evalua PK, si no hay busca campo Identity y si no hay, entonces una combinación de toda la fila. Claro en teroría esta última variante no debe existir.


    CREATE PROCEDURE GetTableKey
    @TableName sysname,
    @PKCols varchar(1000) output,
    @PKSelect varchar(1000) output
    WITH ENCRYPTION
    AS
    declare @fieldname VARCHAR(128)
    BEGIN
    SET NOCOUNT ON;

    --Determinar la llave única para la tabla.
    -- Plan A. Probar armar la PK
    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

    -- Luego obtener los valores de los campos de la PK
    SELECT @PKSelect = COALESCE(@PKSelect+'+','') + ''''''
    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 -- No hay llave primaria. Plan B
    BEGIN
    --Si no hay PK entonces revisar campo identity
    select @fieldname = name from sys.columns where is_identity=1 and OBJECT_NAME(object_id)=@TableName

    select @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + @fieldname + ' = d.' + @fieldname
    SELECT @PKSelect = COALESCE(@PKSelect+'+','') + ''''''
    END

    IF @PKCols IS NULL -- No hay llave primaria y tampoco campo Identity entonces Plan C
    BEGIN
    --Si no hay PK o Identity entonces tomar todos los campos excepto los que fueron actualizados
    SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + COLUMN_NAME + ' = d.' + COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS pk
    WHERE pk.TABLE_NAME = @TableName

    SELECT @PKSelect = COALESCE(@PKSelect+'+','') + ''''''
    FROM INFORMATION_SCHEMA.COLUMNS pk
    WHERE pk.TABLE_NAME = @TableName
    END
    END
    GO

    Estoy de acuerdo a los campos varbinary, lamentablemente la BD es de un tercero y no puedo realizarle modificaciones y las posibilidades de realizarlas son bajas. Por lo pronto solucioné este inconveniente creando la lista de campos de interés.


    SELECT Campo1, Campo2, CampoN into #ins from inserted
    SELECT Campo1, Campo2, CampoN into #ins from deleted

    Para lograr tambien un poco mas de generalidad modifiqué la línea

    SELECT @TableName='Tabla'

    por este otro código. En tiempo de corrida es posible saber que tabla está activaa partir de identificar el trigger que se ejecuta. @ProcID y @obj_id son enteros


    select @ProcID = @@PROCID; --Obtengo el trigger de forma dinámica
    select @obj_id = parent_id from sys.triggers where object_id=@ProcID; --Obtengo la tabla asociada al trigger
    select @TableName=name from sys.objects where object_id=@obj_id --Obtengo su nombre

    Con esta modificación se puede lograr que el trigger sea identico para todas las tablas ya que no habría necesitar ir especificando el nombre de cada tabla en cada trigger.

    Bueno espero que sea de utilidad y saludos a todos.

    Nos leemos

    • Muchas gracias Ariel por tu aporte!! vamos a probarlo de inmediato. Estas cordialmente invitado a colaborar con el blog cuando gustes

  • Me da mucha pena, pero necesito ayuda, pues he corrido el script tal y cual esta, con las mismas tablas y todo y pues me tira el siguiente error

    El nombre de columna ‘TipoTrn’ no es válido.

    Y realmente no le he hecho ningun cambio, y vengo a solicitarles su ayuda pues ya le meti mano al script pero nada, estoy con sql server 2012 express

  • No puede encontrarle solucion a las tablas de ejemplo.
    Pero al probarlas en las que necesitaba auditar corrio bien sin problemas! A testear.

  • Hola Conoces la manera de ver el registro de los cambios que ha sufrido un campo de una tabla en especifico , hablando de movimientos antiguos sobre el campo.

    • Hola Leonardo: SQL Server de caja no trae esa funcionalidad, hay que implementarla. Por lo tanto no hay manera de ver un registro de cambios de un campo a no ser que uses algún mecanismo de auditoría, como el que se sugiere en este artículo.

      También puedes implementar Change Data Capture que está disponible desde la versión 2008. En este enlace puedes averiguar más al respecto:
      https://technet.microsoft.com/en-us/library/bb522649(v=sql.105).aspx

      Suerte

      Alberto

  • Hola buenas, alguien pudo probar el procedimiento de almacenado de ‘Ariel Martin’ no se como aplicarlo, la verdad que estuve probando el procedimiento y no me salia nada de nada por favor si alguien aplico ese procedimiento me podría guiar como aplicarlo. 

    • Hola Juan, primeramente quiero disculparme con el Foro porque cometí un error al pegar el procedimiento anterior y habían faltado datos en los select que daba como resultado que devolvía los valores vacios. Ahora revisando mi implementación me doy cuenta del error y pongo la rutina completa nuevamente.

      CREATE PROCEDURE GetTableKey
      @TableName sysname,
      @PKCols varchar(1000) output,
      @PKSelect varchar(1000) output
      WITH ENCRYPTION
      AS
      declare @fieldname VARCHAR(128)
      BEGIN
      SET NOCOUNT ON;

      --Determinar la llave única para la tabla.
      -- Plan A. Probar armar la PK
      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

      -- Luego obtener los valores de los campos de la PK
      SELECT @PKSelect = COALESCE(@PKSelect+'+','') + ''''''
      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 -- No hay llave primaria. Plan B
      BEGIN
      --Si no hay PK entonces revisar campo identity
      select @fieldname = name from sys.columns where is_identity=1 and OBJECT_NAME(object_id)=@TableName

      select @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + @fieldname + ' = d.' + @fieldname
      SELECT @PKSelect = COALESCE(@PKSelect+'+','') + ''''''
      END

      IF @PKCols IS NULL -- No hay llave primaria y tampoco campo Identity entonces Plan C
      BEGIN
      --Si no hay PK o Identity entonces tomar todos los campos excepto los que fueron actualizados
      SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + COLUMN_NAME + ' = d.' + COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS pk
      WHERE pk.TABLE_NAME = @TableName

      SELECT @PKSelect = COALESCE(@PKSelect+'+','') + ''''''
      FROM INFORMATION_SCHEMA.COLUMNS pk
      WHERE pk.TABLE_NAME = @TableName
      END
      END
      GO

      Hice una corrida sobre una base de trabajo llamada NAE que tiene un llave primaria cuyo campo se llama NAECOD utilizando el siguiente código
      declare @O1 varchar(1000), @O2 varchar(1000)

      exec GetTableKey 'NAE',@O1 output,@O2 output
      select @O1,@O2

      y el resultado es el siguiente:

      @O1 = 'on i.NAECOD = d.NAECOD'
      @O2 = ''

      Saludos a todos y disculpas por el imperdonable error de copia y pegado. Por favor Alberto si puedes esconde el código del post anterior.

      Feliz navidad y un bello año 2017

      • Y vuelvo a dejar un valor en blanco… definitivamente tengo que ponerme espejuelos o cambiar el teclado/mouse.

        El valor de @O2 = ” en la salida

        Saludos a todos.

  • Alberto y el foro disculpen. evidentemente estoy haciendo algo mal al pegar el código porque se desaparece parte del mismo. Podrían indicarme la forma correcta para que quede similar al original. Por favor Alberto no confirmes los post anteriores porque les vuelve a faltar una parte del texto que puse. Un saludo. Ariel

  • Hola que tal, antes que nada agradezco el aporte ya que esta solucion me ha servido mucho.

    Tengo  1 duda espero me puedan ayudar:

    Lo que sucede es que el trigger genera dentro de la tabla de auditoria (LogTransacciones) 1 registro por cada columna afectada en el caso de los update, pero de igual forma cuando se trata de un Insert o Delete.

    Yo necesito que en el particular caso de los Insert o Delete solamente me genere 1 solo registro en la tabla logTransacciones , con algunos datos como referencia como el Pk.

    De antemano agradezco el espacio.

    • Gustavo, este trigger genera un registro por cada campo insertado o eliminado en cada acción de INSERT o UPDATE. Habría que adaptarlo para lograr lo que tu requieres. Te animo a tí o a cualquier miembro de la comunidad a modificar el programa

      Saludos

    • Mauro. Nos han hecho varias vece esa consulta. Al ser este un foro de SQL Server, no hemos “traducido” aun el trigger. Pero animamos a la comunidad a que nos apoyen!

    • Mauro. Nos han hecho varias vece esa consulta. Al ser este un foro de SQL Server, no hemos “traducido” aun el trigger. Pero animamos a la comunidad a que nos apoyen!

    • Hola, lamentablemente no tenemos experiencia en esa marca de base de datos, pero así como existe este blog para SQL Server, seguramente habrá alguno sobre postgres. Suerte en la búsqueda!

  • Hola,

    Excelente trigger muy útil y practico,

    Solamente quería pedirte ayuda, veo que cuanto el valor inicial es NULL o ‘ ‘ (vacio) y se hace un update o un insert a la tabla Cliente no inserta el registro en la tabla de logTransacciones.

    Saludos.

  • Hola,

    Excelente Trigger, muy útil y práctico.

    Queria pedirte ayuda, estoy teniendo problemas cuando el valor inicial de la tabla empleado se inserta como NULL o ‘ ‘ (vacio) no registra en la tabla logTransacciones, al igual con el update, si el valor que voy a actualizar es NULL o ‘ ‘ (vacio) no registra el update realizado a la tabla Cliente en la tabla logTransacciones.

    Tu amable ayuda,

    Gracias.

Deja un comentario

Tu dirección de correo electrónico no será publicada.