SQLTableHistory es una utilidad que hicimos junto a un compañero que nos permite llevar un rastro de auditoria de tablas especificas que podemos seleccionar desde la aplicacion, mantiendo asi un registro de cada accion(insertar, actualizar, eliminar) que involucre las tablas que se le quiera hacer la auditoria.
Requerimientos
- .Net Framework 3.5
- Sql Server 2000-2005
Arquitectura
La aplicacion consta de de una sola tabla llamada AUDIT donde guarda cada uno de los registros de las tablas, guardando asi el nombre de la tabla, nombre de la columna que fue afectada el valor anterior y el valor nuevo de la columna, el usuario de la transaccion, fecha y asi tambien el tipo de transaccion que fue realizada(insertar, actualizar, eliminar).
Para ser posible esto, la utilidad nos crea tres triggers por cada una de las tablas, uno para insertar, actualizar e eliminar.
Script:
Tabla Audit:
/****** Object: Default [DF_Audit_Date] Script Date: 02/22/2008 19:27:40 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Audit_Date]’) AND type = ‘D’) BEGIN ALTER TABLE [dbo].[Audit] DROP CONSTRAINT [DF_Audit_Date]
END GO /****** Object: Table [dbo].[Audit] Script Date: 02/22/2008 19:27:40 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1) DROP TABLE [dbo].[Audit] GO /****** Object: Table [dbo].[Audit] Script Date: 02/22/2008 19:27:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1) BEGIN CREATE TABLE [dbo].[Audit]( [AuditID] [int] IDENTITY(1,1) NOT NULL, [Table] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Column] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [OldValue] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NewValue] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [UserID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Date] [datetime] NOT NULL, [Type] [int] NOT NULL, CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED ( [AuditID] ASC ) ) END GO /****** Object: Default [DF_Audit_Date] Script Date: 02/22/2008 19:27:40 ******/ IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Audit_Date]’) AND type = ‘D’) BEGIN ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_Date] DEFAULT (getdate()) FOR [Date]
END GO
Tabla AuditType:
/****** Object: Table [dbo].[AuditType] Script Date: 02/22/2008 19:35:13 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AuditType]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1) DROP TABLE [dbo].[AuditType] GO /****** Object: Table [dbo].[AuditType] Script Date: 02/22/2008 19:35:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AuditType]’) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1) BEGIN CREATE TABLE [dbo].[AuditType]( [AuditTypeID] [int] NOT NULL, [Name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_AuditType] PRIMARY KEY CLUSTERED ( [AuditTypeID] ASC ) ) END GO
Y debemos tener estos tres valores por default en nuestra base de datos: INSERT [dbo].[AuditType] ([AuditTypeID], [Name]) VALUES (1, convert(text, N’Agregar’ collate SQL_Latin1_General_CP1_CI_AS)) INSERT [dbo].[AuditType] ([AuditTypeID], [Name]) VALUES (2, convert(text, N’Remover’ collate SQL_Latin1_General_CP1_CI_AS)) INSERT [dbo].[AuditType] ([AuditTypeID], [Name]) VALUES (3, convert(text, N’Modificar’ collate SQL_Latin1_General_CP1_CI_AS))
Clases
Usando Linq to SQL y a la vez el Designer, tenemos las siguientes clases:
Donde:
SysTable: es la clase que contiene todas las tablas de nuestra base de datos seleccionada, se conecta a la tabla sys.tables
Table: clase que contiene el schema de las tablas, se conecta a la tabla INFORMATION_SCHEMA.TABLES
Trigger: es la clase que contiene los triggers de la base de datos, se usa la tabla del sistema sys.triggers
Column: clase que contiene el schema de las columnas de cada tabla de nuestra base de datos, para obtener esto se usa la tabla del sistema INFORMATION_SCHEMA.COLUMNS
Si se fijan para todo esto usamos tablas del sistema, ya que estas nos pueden proveer el schema de las tablas y poder asi crear los triggers de una manera dinamica.
Deben configurar el archivo app.config en la cadena de conexion y espeficiar el connectionstring correspondiente a su base de datos… en un futuro lo pondre para que se pueda hacer de manera mas dinamica.
Si les interesa pueden descargar el codigo fuente y asi poder echarle una miradita al codigo.
Preview de la utilidad:
Espero que les sea de utilidad.
|
Estaban, parece que copiaste el script para generar la tabla equivocada 😛 Era «Audit», no «dtAudit».
Heyy si muchas gracias lucas, si lo que pasaba era que tenia una version vieja y se llamaba dtaudit, mil gracias
PingBack desde Agregar un simple Trigger para auditar tu base de datos SQL Server
buenisimo…, por cierto tengo una tabla con 300 campos, es viable utilizar estos triggers para mi tabla o me va a colgar el sql express?
de donde bajo el codigo fuente , gracias ?
Hola ccollado puedes bajarlo desde aqui
http://geeks.ms/files/folders/73645/download.aspx
Pudieramos tener acceso al codigo, fuente, es precisamente lo que estamos buscando hacer
Gracias
no se como hacerlo funcionar para todas las tablas de mibase de datos…
me podria acolaborar con esto gracias
oscar
no lo he podido a funcionar en todas las tablas como hago por favor
Perdon, soy nuevo en esto, pero los script los corro desde el Query Analyzer?
Muchisimas gracias, justo estaba trabajando en un proyecto similar, asi que no reinventare la polvora..
🙂
como capturo el ip del cliente