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. |