sql-server - una - registro de eventos sql server

Crear activadores de auditoría en SQL Server (10)

Cada tabla que uno quiera monitorear, necesitará su propio disparador. Es bastante obvio que, como se señala en la respuesta aceptada, la generación de código será algo bueno.

Si le gusta este enfoque, podría ser una idea utilizar este desencadenador y reemplazar algunos pasos genéricos con código generado para cada tabla por separado.

Sin embargo, creé un Audit-Trigger completamente genérico . La tabla observada debe tener un PK , pero este PK puede ser incluso de varias columnas .

Es posible que algunos tipos de columna (como BLOB) no funcionen, pero puede excluirlos fácilmente.

Este no será el mejor en rendimiento :-D

Para ser sincero: esto es más como un ejercicio ...

SET NOCOUNT ON; GO CREATE TABLE AuditTest(ID UNIQUEIDENTIFIER ,LogDate DATETIME ,TableSchema VARCHAR(250) ,TableName VARCHAR(250) ,AuditType VARCHAR(250),Content XML); GO

--Algunas tablas para probar esto (utilizaron columnas PK peculiares a propósito ...)

CREATE TABLE dbo.Testx(ID1 DATETIME NOT NULL ,ID2 UNIQUEIDENTIFIER NOT NULL ,Test1 VARCHAR(100) ,Test2 DATETIME); --Add a two column PK ALTER TABLE dbo.Testx ADD CONSTRAINT PK_Test PRIMARY KEY(ID1,ID2);

- Algunos datos de prueba

INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES ({d''2000-01-01''},NEWID(),''Test1'',NULL) ,({d''2000-02-01''},NEWID(),''Test2'',{d''2002-02-02''});

- Este es el contenido actual

SELECT * FROM dbo.Testx; GO

--El disparador de la auditoría

CREATE TRIGGER [dbo].[UpdateTestTrigger] ON [dbo].[Testx] FOR UPDATE,INSERT,DELETE AS BEGIN IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN; SET NOCOUNT ON; DECLARE @tableSchema VARCHAR(250); DECLARE @tableName VARCHAR(250); DECLARE @AuditID UNIQUEIDENTIFIER=NEWID(); DECLARE @LogDate DATETIME=GETDATE(); SELECT @tableSchema = sch.name ,@tableName = tb.name FROM sys.triggers AS tr INNER JOIN sys.tables AS tb ON tr.parent_id=tb.object_id INNER JOIN sys.schemas AS sch ON tb.schema_id=sch.schema_id WHERE tr.object_id = @@PROCID DECLARE @tp VARCHAR(10)=CASE WHEN EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted) THEN ''upd'' ELSE CASE WHEN EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) THEN ''del'' ELSE ''ins'' END END; SELECT * INTO #tmpInserted FROM inserted; SELECT * INTO #tmpDeleted FROM deleted; SELECT kc.ORDINAL_POSITION, kc.COLUMN_NAME INTO #tmpPKColumns FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON tc.TABLE_CATALOG=kc.TABLE_CATALOG AND tc.TABLE_SCHEMA=kc.TABLE_SCHEMA AND tc.TABLE_NAME=kc.TABLE_NAME AND tc.CONSTRAINT_NAME=kc.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE=''PRIMARY KEY'' WHERE tc.TABLE_SCHEMA=@tableSchema AND tc.TABLE_NAME=@tableName ORDER BY kc.ORDINAL_POSITION; DECLARE @pkCols VARCHAR(MAX)= STUFF ( ( SELECT ''UNION ALL SELECT '''''' + pc.COLUMN_NAME + '''''' AS [@name] , CAST(COALESCE(i.'' + QUOTENAME(pc.COLUMN_NAME) + '',d.'' + QUOTENAME(pc.COLUMN_NAME) + '') AS VARCHAR(MAX)) AS [@value] '' FROM #tmpPKColumns AS pc ORDER BY pc.ORDINAL_POSITION FOR XML PATH('''') ),1,16,''''); DECLARE @pkColsCompare VARCHAR(MAX)= STUFF ( ( SELECT ''AND i.'' + QUOTENAME(pc.COLUMN_NAME) + ''=d.'' + QUOTENAME(pc.COLUMN_NAME) FROM #tmpPKColumns AS pc ORDER BY pc.ORDINAL_POSITION FOR XML PATH('''') ),1,3,''''); DECLARE @cols VARCHAR(MAX)= STUFF ( ( SELECT '','' + CASE WHEN @tp=''upd'' THEN ''CASE WHEN (i.['' + COLUMN_NAME + '']!=d.['' + COLUMN_NAME + ''] '' + ''OR (i.['' + COLUMN_NAME + ''] IS NULL AND d.['' + COLUMN_NAME + ''] IS NOT NULL) '' + ''OR (i.[''+ COLUMN_NAME + ''] IS NOT NULL AND d.['' + COLUMN_NAME + ''] IS NULL)) '' + ''THEN '' ELSE '''' END + ''(SELECT '''''' + COLUMN_NAME + '''''' AS [@name]'' + CASE WHEN @tp IN (''upd'',''del'') THEN '',ISNULL(CAST(d.['' + COLUMN_NAME + ''] AS NVARCHAR(MAX)),N''''##NULL##'''') AS [@old]'' ELSE '''' END + CASE WHEN @tp IN (''ins'',''upd'') THEN '',ISNULL(CAST(i.['' + COLUMN_NAME + ''] AS NVARCHAR(MAX)),N''''##NULL##'''') AS [@new] '' ELSE '''' END + '' FOR XML PATH(''''Column''''),TYPE) '' + CASE WHEN @tp=''upd'' THEN ''END'' ELSE '''' END FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@tableSchema AND TABLE_NAME=@tableName FOR XML PATH('''') ),1,1,'''' ); DECLARE @cmd VARCHAR(MAX)= ''SET LANGUAGE ENGLISH; WITH ChangedColumns AS ( SELECT A.PK'' + '',A.PK.query(''''data(/PK/Column/@value)'''').value(''''text()[1]'''',''''nvarchar(max)'''') AS PKVals'' + '',Col.* FROM #tmpInserted AS i FULL OUTER JOIN #tmpDeleted AS d ON '' + @pkColsCompare + '' CROSS APPLY ( SELECT '' + @cols + '' FOR XML PATH(''''''''),TYPE ) AS Col([Column]) CROSS APPLY(SELECT (SELECT tbl.* FROM (SELECT '' + @pkCols + '') AS tbl FOR XML PATH(''''Column''''), ROOT(''''PK''''),TYPE)) AS A(PK) ) INSERT INTO AuditTest(ID,LogDate,TableSchema,TableName,AuditType,Content) SELECT '''''' + CAST(@AuditID AS VARCHAR(MAX)) + '''''','''''' + CONVERT(VARCHAR(MAX),@LogDate,126) + '''''','''''' + @tableSchema + '''''','''''' + @tableName + '''''','''''' + @tp + '''''' ,( SELECT '''''' + @tableSchema + '''''' AS [@TableSchema] ,'''''' + @tableName + '''''' AS [@TableName] ,'''''' + @tp + '''''' AS [@ActionType] ,( SELECT ChangedColumns.PK AS [*] ,( SELECT x.[Column] AS [*],'''''''' FROM ChangedColumns AS x WHERE x.PKVals=ChangedColumns.PKVals FOR XML PATH(''''Values''''),TYPE ) FROM ChangedColumns FOR XML PATH(''''Row''''),TYPE ) FOR XML PATH(''''Changes'''') );''; EXEC (@cmd); DROP TABLE #tmpInserted; DROP TABLE #tmpDeleted; END GO

- Ahora vamos a probarlo con algunas operaciones:

UPDATE dbo.Testx SET Test1=''New 1'' WHERE ID1={d''2000-01-01''}; UPDATE dbo.Testx SET Test1=''New 1'',Test2={d''2000-01-01''} ; DELETE FROM dbo.Testx WHERE ID1={d''2000-02-01''}; DELETE FROM dbo.Testx WHERE ID1=GETDATE(); --no affect INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES ({d''2000-03-01''},NEWID(),''Test3'',{d''2001-03-03''}) ,({d''2000-04-01''},NEWID(),''Test4'',{d''2001-04-04''}) ,({d''2000-05-01''},NEWID(),''Test5'',{d''2001-05-05''}); UPDATE dbo.Testx SET Test2=NULL; --all rows DELETE FROM dbo.Testx WHERE ID1 IN ({d''2000-02-01''},{d''2000-03-01''}); GO

--Verifique el estado final

SELECT * FROM dbo.Testx; SELECT * FROM AuditTest; GO

- Limpie (¡ cuidado con datos reales! )

DROP TABLE dbo.Testx; GO DROP TABLE dbo.AuditTest; GO

El resultado de la inserción

El resultado selectivo de una actualización

Y el resultado de una eliminación

<Changes TableSchema="dbo" TableName="Testx" ActionType="del"> <Row> <PK> <Column name="ID1" value="Mar 1 2000 12:00AM" /> <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" /> </PK> <Values> <Column name="ID1" old="Mar 1 2000 12:00AM" /> <Column name="ID2" old="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" /> <Column name="Test1" old="Test3" /> <Column name="Test2" old="##NULL##" /> </Values> </Row> </Changes>

Necesito implementar el seguimiento de cambios en dos tablas en mi base de datos de SQL Server 2005. Necesito auditar adiciones, eliminaciones, actualizaciones (con detalles sobre lo que se actualizó). Estaba planeando usar un disparador para hacer esto, pero después de hurgar en Google, descubrí que era increíblemente fácil hacer esto incorrectamente, y quería evitar eso en el arranque.

¿Alguien puede publicar un ejemplo de un activador de actualización que logra esto con éxito y de una manera elegante? Espero terminar con una tabla de auditoría con la siguiente estructura:

CARNÉ DE IDENTIDAD
LogDate
Nombre de la tabla
Tipo de transacción (actualizar / insertar / eliminar)
RecordID
Nombre del campo
Valor antiguo
Nuevo valor

... pero estoy abierto a sugerencias.

¡Gracias!

Estamos utilizando ApexSQL Audit que genera activadores de auditoría y debajo están las estructuras de datos utilizadas por esta herramienta. Si no planea comprar una solución de terceros, puede instalar esta herramienta en modo de prueba, ver cómo implementaron desencadenadores y almacenamiento y luego crear algo similar para usted.

No me molesté en entrar en muchos detalles sobre cómo funcionan estas tablas, pero espero que esto te ayude a empezar.

Finalmente encontré una solución universal, que no requiere cambios dinámicos de SQL y registros de todas las columnas.

No es necesario cambiar el disparador si la tabla cambia.

Este es el registro de auditoría:

CREATE TABLE [dbo].[Audit]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Type] [char](1) COLLATE Latin1_General_CI_AS NULL, [TableName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL, [PK] [int] NULL, [FieldName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL, [OldValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [NewValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL, [UpdateDate] [datetime] NULL, [Username] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL, CONSTRAINT [PK_AuditB] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Este es el disparador de una tabla:

INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username) SELECT CASE WHEN NOT EXISTS (SELECT ID FROM deleted WHERE ID = ISNULL(ins.PK,del.PK)) THEN ''I'' WHEN NOT EXISTS (SELECT ID FROM inserted WHERE ID = ISNULL(ins.PK,del.PK)) THEN ''D'' ELSE ''U'' END as [Type], ''AGB'' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM (SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.insRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select i.ID as PK, i.LastModifiedBy as Username, convert(xml, (select i.* for xml raw)) as insRowCol from inserted as i ) as insRowTbl CROSS APPLY insRowTbl.insRowCol.nodes(''/row/@*'') as attr(insRow) ) as ins FULL OUTER JOIN (SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.delRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select d.ID as PK, d.LastModifiedBy as Username, convert(xml, (select d.* for xml raw)) as delRowCol from deleted as d ) as delRowTbl CROSS APPLY delRowTbl.delRowCol.nodes(''/row/@*'') as attr(delRow) ) as del on ins.PK = del.PK and ins.FieldName = del.FieldName WHERE isnull(ins.FieldName,del.FieldName) not in (''LastModifiedBy'', ''ID'', ''TimeStamp'') and ((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue))

Este disparador es para una tabla llamada AGB. La tabla con el nombre AGB tiene una columna clave principal con el nombre ID y una columna con el nombre LastModifiedBy que contiene el nombre de usuario que realizó la última edición.

El desencadenador consta de dos partes, primero convierte columnas de tablas insertadas y eliminadas en filas. Esto se explica en detalle aquí: https://.com/a/43799776/4160788

A continuación, une las filas (una fila por columna) de las tablas insertadas y eliminadas por clave principal y nombre de campo, y registra una línea para cada columna modificada. NO registra cambios de ID, TimeStamp o LastModifiedByColumn.

Puede insertar su propio nombre de tabla, nombres de columnas.

También puede crear el siguiente procedimiento almacenado y luego llamar a este procedimiento almacenado para generar sus activadores:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[_create_audit_trigger]'') AND type in (N''P'', N''PC'')) BEGIN EXEC dbo.sp_executesql @statement = N''CREATE PROCEDURE [dbo].[_create_audit_trigger] AS'' END ALTER PROCEDURE [dbo].[_create_audit_trigger] @TableName varchar(max), @IDColumnName varchar(max) = ''ID'', @LastModifiedByColumnName varchar(max) = ''LastModifiedBy'', @TimeStampColumnName varchar(max) = ''TimeStamp'' AS BEGIN PRINT ''start '' + @TableName + '' ('' + @IDColumnName + '', '' + @LastModifiedByColumnName + '', '' + @TimeStampColumnName + '')'' /* if you have other audit trigger on this table and want to disable all triggers, enable this: EXEC (''ALTER TABLE '' + @TableName + '' DISABLE TRIGGER ALL'')*/ IF EXISTS (SELECT * FROM sys.objects WHERE [type] = ''TR'' AND [name] = ''tr_audit_''+@TableName) EXEC (''DROP TRIGGER [dbo].tr_audit_''+@TableName) EXEC ('' CREATE TRIGGER [dbo].[tr_audit_''+@TableName+''] ON [ILSe].[dbo].[''+@TableName+''] FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username) SELECT CASE WHEN NOT EXISTS (SELECT ''+@IDColumnName+'' FROM deleted WHERE ''+@IDColumnName+'' = ISNULL(ins.PK,del.PK)) THEN ''''I'''' WHEN NOT EXISTS (SELECT ''+@IDColumnName+'' FROM inserted WHERE ''+@IDColumnName+'' = ISNULL(ins.PK,del.PK)) THEN ''''D'''' ELSE ''''U'''' END as [Type], ''''''+@TableName+'''''' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM (SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value(''''local-name(.)'''', ''''nvarchar(128)'''') as FieldName, attr.insRow.value(''''.'''', ''''nvarchar(max)'''') as FieldValue FROM (Select i.''+@IDColumnName+'' as PK, i.''+@LastModifiedByColumnName+'' as Username, convert(xml, (select i.* for xml raw)) as insRowCol from inserted as i) as insRowTbl CROSS APPLY insRowTbl.insRowCol.nodes(''''/row/@*'''') as attr(insRow)) as ins FULL OUTER JOIN (SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value(''''local-name(.)'''', ''''nvarchar(128)'''') as FieldName, attr.delRow.value(''''.'''', ''''nvarchar(max)'''') as FieldValue FROM (Select d.''+@IDColumnName+'' as PK, d.''+@LastModifiedByColumnName+'' as Username, convert(xml, (select d.* for xml raw)) as delRowCol from deleted as d) as delRowTbl CROSS APPLY delRowTbl.delRowCol.nodes(''''/row/@*'''') as attr(delRow)) as del on ins.PK = del.PK and ins.FieldName = del.FieldName WHERE isnull(ins.FieldName,del.FieldName) not in (''''''+@LastModifiedByColumnName+'''''', ''''''+@IDColumnName+'''''', ''''''+@TimeStampColumnName+'''''') and ((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue)) END '') PRINT ''end '' + @TableName PRINT '''' END

Hay una forma genérica de hacerlo.

CREATE TABLE [dbo].[Audit]( [TYPE] [CHAR](1) NULL, [TableName] [VARCHAR](128) NULL, [PK] [VARCHAR](1000) NULL, [FieldName] [VARCHAR](128) NULL, [OldValue] [VARCHAR](1000) NULL, [NewValue] [VARCHAR](1000) NULL, [UpdateDate] [datetime] NULL, [UserName] [VARCHAR](128) NULL ) ON [PRIMARY]

Mike, estamos usando la herramienta www.auditdatabase.com, esta herramienta gratuita genera activadores de auditoría y funciona bien con SQL Server 2008 y 2005 y 2000. Es una herramienta sofisticada y sólida que permite activadores de auditoría personalizados para una tabla.

Otra excelente herramienta es Apex SQL Audit

No hay una forma genérica de hacerlo de la manera que desee. Finalmente terminas escribiendo resmas de código para cada mesa. Sin mencionar que puede ser increíblemente lento si necesita comparar cada columna para el cambio.

Además, el hecho de que pueda estar actualizando varias filas al mismo tiempo implica que necesita abrir un cursor para recorrer todos los registros.

De la forma en que lo haré, usaré una tabla con una estructura idéntica a las tablas que está rastreando y, luego, lo desentrearé para mostrar qué columnas realmente han cambiado. También mantendría un registro de la sesión que realmente cambió. Esto supone que tiene la clave principal en la tabla que se rastrea.

Entonces, dado una mesa como esta

CREATE TABLE TestTable (ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY, Name1 NVARCHAR(40) NOT NULL, Name2 NVARCHAR(40))

Crearía una tabla de auditoría como esta en la auditoría schmea.

CREATE TABLE Audit.TestTable (SessionID UNIQUEIDENTIFER NOT NULL, ID INT NOT NULL, Name1 NVARCHAR(40) NOT NULL, Name2 NVARCHAR(40), Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In ''Deleted'',''Updated''), RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in ''New'',''Old'',''Deleted''), ChangedDate DATETIME NOT NULL Default GETDATE(), ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())

Y un desencadenador de actualización como este

CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS BEGIN SET NOCOUNT ON DECLARE @SessionID UNIQUEIDENTIFER SET @SessionID = NEWID() INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID) SELECT ID,name1,Name2,''Updated'',''Old'',@SessionID FROM Deleted INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID) SELECT ID,name1,Name2,''Updated'',''New'',@SessionID FROM Inserted END

Esto funciona bastante rápido. Durante la creación de informes, simplemente se une a las filas en función del ID de sesión y la clave principal y genera un informe. Alternativamente, puede tener un trabajo por lotes que recorre periódicamente todas las tablas de la tabla de auditoría y preparar un par de nombre-valor que muestre los cambios.

HTH

Se usa el disparador Si modifica o inserta en una tabla en particular, se ejecutará y podrá verificar la columna en particular en el desencadenante. El ejemplo completo con explicación se encuentra en el siguiente sitio web. http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html

Solo quiero mencionar un par de puntos:

Use generadores de código No puede tener un solo procedimiento para seguir todas las tablas, deberá generar activadores similares pero distintos en cada tabla rastreada. Este tipo de trabajo es el más adecuado para la generación automática de código. En su lugar usaría una transformación XSLT para generar el código de XML, y el XML se puede generar automáticamente a partir de metadatos. Esto le permite mantener fácilmente los desencadenantes al regenerarlos cada vez que realiza un cambio en la estructura / lógica de auditoría o se agrega / modifica una tabla de objetivos.

Considere la planificación de la capacidad para la auditoría. Una tabla de auditoría que rastree todos los cambios de valores será, de lejos, la tabla más grande en la base de datos: contendrá todos los datos actuales y todo el historial de los datos actuales. Dicha tabla aumentará el tamaño de la base de datos en 2-3 órdenes de magnitud (x10, x100). Y la tabla de auditoría se convertirá rápidamente en el cuello de botella de todo:

cada operación DML requerirá bloqueos en la tabla de auditoría
todas las operaciones administrativas y de mantenimiento tendrán que acomodar el tamaño de la base de datos debido a la auditoría

Tenga en cuenta los cambios de esquema . Se puede descartar una tabla llamada ''Foo'' y luego se puede crear una tabla diferente llamada ''Foo''. La pista de auditoría debe ser capaz de distinguir los dos objetos diferentes. Mejor utilizar un enfoque de dimensión que cambia lentamente .

Considere la necesidad de eliminar de manera eficiente los registros de auditoría. Cuando se vence el período de retención dictado por las políticas de su aplicación, debe poder eliminar los registros de auditoría correspondientes. Puede que no parezca tan importante ahora, pero 5 años después, cuando los primeros registros venzan, la tabla de auditoría ha crecido a 9.5TB y puede ser un problema.

Considere la necesidad de consultar la auditoría . La estructura de la tabla de auditoría debe estar preparada para responder de manera eficiente a las consultas de auditoría. Si su auditoría no puede ser consultada, entonces no tiene ningún valor. Las consultas dependerán exclusivamente de sus requisitos y solo usted los conocerá, pero la mayoría de los registros de auditoría se consultan por intervalos de tiempo (''¿qué cambios ocurrieron entre las 7 p.m. y las 8 pm de ayer?''), Por objeto (''qué cambios se produjeron en este registro en este ¿tabla? '') o por autor ('' ¿qué cambios hizo Bob en la base de datos? '').

Voy a arrojar mi enfoque y sugerencias a la mezcla.

Tengo una tabla muy similar a su diseño propuesto que he utilizado durante los últimos siete años en una base de datos SQL 2005 (ahora 2008).

Agregué insertar, actualizar y eliminar disparadores a las tablas seleccionadas, y luego verifiqué los cambios a los campos seleccionados. En ese momento era simple y funciona bien.

Aquí están los problemas que encuentro con este enfoque:

Los campos de valor antiguo / nuevo de la tabla de auditoría tenían que ser de tipo varchar (MAX) para poder manejar todos los diferentes valores que podrían auditarse: int, bool, decimal, float, varchar, etc. todos deben ajustarse
El código para verificar cada campo es tedioso para escribir y mantener. También es fácil pasar cosas por alto (como cambiar un campo nulo a un valor que no se atrapó, porque NULL! = Value es NULL.
Eliminar registro: ¿cómo se graba esto? ¿Todos los campos? ¿Los seleccionados? Se vuelve complicado

Mi visión futura es usar algún código SQL-CLR y escribir un disparador genérico que se ejecute y verifique los metadatos de la tabla para ver qué auditar. En segundo lugar, los valores Nuevos / Viejos se convertirán en campos XML y el objeto entero grabado: esto da como resultado más datos, pero una eliminación tiene un registro completo. Hay varios artículos en la web sobre activadores de auditoría XML.

CREATE TRIGGER TriggerName ON TableName FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @ExecStr varchar(50), @Qry nvarchar(255) CREATE TABLE #inputbuffer ( EventType nvarchar(30), Parameters int, EventInfo nvarchar(255) ) SET @ExecStr = ''DBCC INPUTBUFFER('' + STR(@@SPID) + '')'' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) SELECT @Qry AS ''Query that fired the trigger'', SYSTEM_USER as LoginName, USER AS UserName, CURRENT_TIMESTAMP AS CurrentTime END