SQLTableHistory utilidad para hacerle auditoria a tus tablas

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.

tableaudit

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:

clasese

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:

app

Espero que les sea de utilidad.

12 comentarios sobre “SQLTableHistory utilidad para hacerle auditoria a tus tablas”

Responder a anonymous Cancelar respuesta

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