Despues de pasar muchas horas pensando, debuggiando y buscandole la vuelta a una solucion que consistia en hacerle una auditoria a una base de datos SQL 2005, pudimos desarrollar una herramienta llamada SQLTableHistory esta herramienta nos da la facilidad de que podemos seleccionar las tablas que queremos auditar.
Pero navegando me encontre con una excelente solucion hecha por Jon Galloway, en la cual consiste en crear un solo script y que este se encarge de crear los triggers necesarios para cada tabla de la base de datos seleccionada, la diferencia con SQLTableHistory es que con nuestra herramienta podemos seleccionar las tablas que queremos que se le haga su respectiva auditoria y con la solucion presentada en este blog.
Aqui un ejemplo de como se verian los datos con la solucion de Jon Galloway
Con esta informacion podemos hacer reportes de una manera sencilla tales como:
Con estas herramientas es posible volver al estado anterior de la tabla y volver en un punto especifico, claro esta ya depende de las nuevas extensiones que ustedes le hagan.
Aqui os dejo el script completo que crea la tabla de auditoria y corre el script para crear los triggers a todas las tablas de la base de datos.
USE MYDATABASE–Ponerle el nombre de la base de datos que va hacer auditada
GO
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Audit’)
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserNamevarchar(128)
)
GO
DECLARE @sql varchar(8000), @TABLE_NAMEsysname
SET NOCOUNT ON
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
WHILE @TABLE_NAMEIS NOT NULL
BEGIN
EXEC(‘IF OBJECT_ID (»’ + @TABLE_NAME+ ‘_ChangeTracking», »TR») IS NOT NULL DROP TRIGGER ‘ + @TABLE_NAME+ ‘_ChangeTracking’)
SELECT @sql =
‘
create trigger ‘ + @TABLE_NAME+ ‘_ChangeTracking on ‘ + @TABLE_NAME+ ‘ for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = »’ + @TABLE_NAME+ »’
— date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + » » + convert(varchar(12), getdate(), 114)
— Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = »U»
else
select @Type = »I»
else
select @Type = »D»
— get list of columns
select * into #ins from inserted
select * into #del from deleted
— Get primary key columns for full outer join
select@PKCols = coalesce(@PKCols + » and», » on») + » i.» + c.COLUMN_NAME + » = d.» + c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = »PRIMARY KEY»
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
— Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+»+»,»») + »»»» + COLUMN_NAME + »»»»
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = »PRIMARY KEY»
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @PKValueSelect = coalesce(@PKValueSelect+»+»,»») + »convert(varchar(100), coalesce(i.» + COLUMN_NAME + »,d.» + 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
if @PKCols is null
begin
raiserror(»no PK on table %s», 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field – 1 )% 8 + 1
select @bit = power(2,@bit – 1)
select @char = ((@field – 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (»I»,»D»)
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = »insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)»
select @sql = @sql + » select »»» + @Type + »»»»
select @sql = @sql + »,»»» + @TableName + »»»»
select @sql = @sql + »,» + @PKFieldSelect
select @sql = @sql + »,» + @PKValueSelect
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
‘
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
END
Para mas informacion:
Jon Galloway
SQL Server 2005’s DDL triggers
Nigel Rivett’s SQL Server Auditing triggers
Espero que les sea de utilidad!!!
El problema de este tipo de auditorias es que no te dice quien ha realizado tal acción. Si tuvieses SingleSOn en la aplicación, aun se podría auditar de esta manera, pero si no, imposible. Habria que modificar las aplicaciones que modifican la base de datos con algo montado sobre Entreprise Library o Log4NET.
Carlos,
con este tipo de auditorías se puede obtener el usuario si con pequeños cambios a la aplicación.
Este sitio tiene información de como hacerlo:
http://www.auditdatabase.com
a mi me sirvió..
saludos,
Próspero
Esto esta muy bueno muchas gracias!!,
en cuanto al nombre de usuario @UserName dara como resultado el «nombre de la maquinanombre de la cuenta windows» en el caso de que se use autenticacion de windows para acceder a la base de datos, pero si se usa la autenticacion de sql server entonces la variable @UserName contedrá el nombre de usuario, por lo que se puede saber que usuario hizo que cambio. Muy bueno!!!
Yo manejo un esquema similar a este solo que con plantillas para generar los triggers, el caso es que siempre me encuentro con la dificultad de conocer el nombre de la transaccion o el nombre del SP que realizó el insert y por lo tanto que desencadenó el trigger.
hace rato que busco una solucion a este tema. te agradeceria si puedes ayudarme con eso
Hola:
el aporte esta muy bueno.
solamente una observacion, cuando en el codigo se crea la variable @sql encontre en SQL 2000 que si esta concatenacion tiene un largo de mas de 4000 da error ( y el error que da es totalmente desorientate porque da error como si estuviera algo mas escrito en el codigo).
la solucion fue dividir el codigo en dos
@sql = «select campos… bla bla bla»
@sql = @sql + «aca sigue el codigo»
Espero que le ahorre a alguien el dolor de cabeza que me dio esto a mi ayer jajaja.
saludos
Muy buen aporte, particularmente me fue de mucha utilidad.
Felicitaciones.
Bueno, yo creo que una gran idea para resolver el tema de saber quier realizó la acción, de forma facil y sin tener que hacer grandes modificaciones, se encuentra en esta página http://debetta.com/blogs/peter_debetta/archive/2007/07/19/37.aspx
Hola, Muy buen aporte
Muy buen aporte
Que tal,
Estoy probando el codigo, e hice varios cambios porque salian errores. El problema que tengo es que me sale un error al correrlo
Servidor: mensaje 311, nivel 16, estado 1, procedimiento ANULACION_ORDEN_COMPRA_ChangeTracking, línea 35
No se pueden usar columnas de tipo text, ntext o image en las tablas insertadas y eliminadas.
Tengo varias tablas con campos de tipo TEXT, como podria solucionar esto ??
Gracias de antemano.
John
@Carlos: Se puede hacer algun apaño 😉
Solo necesitas modificar la aplicación para que le pase a un proceso almacenado el «usuario». No el usuario de BBDD sino el usuario de la aplicación que se logeará contra una tabla de la BBDD.
Una vez le pasas ese usuario, lo guardas en el context_info de la sesion, y añades una columna a la tabla de esta aplicación que tenga el valor por defecto context_info.
El context_info pertenece a cada sesión, lo puedes leer de la vista de compatibilidad master.dbo.sysprocesses y se borra cuando el usuario abandona la sesion.
este script no sirve
saludos
este trigger es para auditar tablas y quisiera felicitar al creador y a todos los que dan sus opiniones. mi enterrogante es como auditar la creacion de procedimientos almacenados.
Tengo una aplicacion en la cual cada usuario tiene un procedimiento almacenado que engloba todos sus derechos sobre la aplicacion. mi intencion es lograr llenar una tabla con los procedimientos almacenados disponibles para asignar a los usuarios, de esta manera los administradores del sistema (que no son informaticos), ya tendrian en una tabla cuales procedimientos se pueden asignar.
disculpen tanto blablabla.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘SET’.
Msg 137, Level 15, State 1, Line 11
Must declare the variable ‘@TABLE_NAME’.
Msg 137, Level 15, State 2, Line 17
Must declare the variable ‘@TABLE_NAMEIS’.
Msg 137, Level 15, State 2, Line 19
Must declare the variable ‘@TABLE_NAME’.
Msg 137, Level 15, State 2, Line 22
Must declare the variable ‘@TABLE_NAME’.
Msg 137, Level 15, State 1, Line 117
Must declare the variable ‘@TABLE_NAME’.
Encontre un problema que cuando la clave principal es compuesta, por ejemplo id1+id2+id3, sale todo concatenado el nombre y luego los valos de dichos campos, estoy en busca de una solucion para separar los nombres de campos y valores por «;»
alguien puede subir una actualizacion del script que discrimine los campos «text, ntext, image»
gracias
Revizando el script me econtre con los siguientes errores
1. UserNamevarchar(128) es decir el tipo de dato esta pegado al nombre debe quedar asio UserName varchar(128).
2. WHILE @TABLE_NAMEIS NOT NULL, la palabra IS esta pegada a la variable @TABLE_NAME debe quedar asip WHILE @TABLE_NAME IS NOT NULL
Segui revizando y me encontre con este error lo cual no tengo idea que puede ser. Alguien que me colabore gracias.
19:45:56 [USE – 0 row(s), 0.000 secs] Command processed. No rows were affected
Code: 5701 SQL State: 01000 — Cambiado el contexto de base de datos a ‘bombas’.
19:45:56 [IF – 0 row(s), 0.000 secs] Command processed. No rows were affected
19:45:56 [DECLARE – 0 row(s), 0.000 secs] [Error Code: 170, SQL State: 37000] Línea 14: sintaxis incorrecta cerca de ‘=’.
… 3 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 2 warnings, 1 errors]
Alguien que me ayude con este script pero sin errores
Gracias…
Server: Msg 173, Level 15, State 1, Line 28
The definition for column ‘UserNamevarchar’ must include a data type.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘SET’.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable ‘@TABLE_NAME’.
Server: Msg 137, Level 15, State 1, Line 24
Must declare the variable ‘@TABLE_NAMEIS’.
Server: Msg 137, Level 15, State 1, Line 28
Must declare the variable ‘@TABLE_NAME’.
Server: Msg 137, Level 15, State 1, Line 34
Must declare the variable ‘@TABLE_NAME’.
Server: Msg 137, Level 15, State 1, Line 232
Must declare the variable ‘@TABLE_NAME’.
Encontré un problema después del campo 25 o 26 hay un desplazamiento y no he podido encontrar por que se da.
Corrijo algunos errores tipográficos del ejemplo:
USE ¡¡¡¡MYDATABASE!!!! –Ponerle el nombre de la base de datos que va hacer auditada
GO
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Audit’)
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
WHILE @TABLE_NAME IS NOT NULL
BEGIN
EXEC(‘IF OBJECT_ID (»’ + @TABLE_NAME+ ‘_ChangeTracking», »TR») IS NOT NULL DROP TRIGGER ‘ + @TABLE_NAME+ ‘_ChangeTracking’)
SELECT @sql =
‘
create trigger ‘ + @TABLE_NAME+ ‘_ChangeTracking on ‘ + @TABLE_NAME+ ‘ for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = »’ + @TABLE_NAME+ »’
— date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + » » + convert(varchar(12), getdate(), 114)
— Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = »U»
else
select @Type = »I»
else
select @Type = »D»
— get list of columns
select * into #ins from inserted
select * into #del from deleted
— Get primary key columns for full outer join
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
— Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+»+»,»») + »»»» + 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
select @PKValueSelect = coalesce(@PKValueSelect+»+»,»») + »convert(varchar(100), coalesce(i.» + COLUMN_NAME + »,d.» + 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
if @PKCols is null
begin
raiserror(»no PK on table %s», 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield begin select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field – 1 )% 8 + 1
select @bit = power(2,@bit – 1)
select @char = ((@field – 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (»I»,»D»)
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = »insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)»
select @sql = @sql + » select »»» + @Type + »»»»
select @sql = @sql + »,»»» + @TableName + »»»»
select @sql = @sql + »,» + @PKFieldSelect
select @sql = @sql + »,» + @PKValueSelect
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
‘
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
END
Alguien tuvo problemas de este tipo
Msg 208, Level 16, State 1, Line 6
El nombre de objeto ‘INFORMATION_SCHEMA.Tables’ no es válido.
Ejecuto todo el script y ok, pero en la ultima parte me da este error:
Msg 208, Level 16, State 1, Line 6
El nombre de objeto ‘INFORMATION_SCHEMA.Tables’ no es válido.
no tienes los privilegios o tu version de sqlserver es distinta, ‘INFORMATION_SCHEMA.Tables’ yo lo he visto en sqlserver
Como hacer para correr el script en tabla que tiene campos text, ntext, o image..
me sale el sgte error:
Server: Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 59
Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
Server: Msg 311, Level 16, State 1, Procedure dtproperties_ChangeTracking, Line 61
Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
excelente!!! ESTE CODIGO ME CAYO ESPECTACULAR… no saben todo el trabajo que me ahorro.. y lo mejor es que es totalmente dinamico, no importa que luego agregue una nueva columna, ya automaticamente tambien la audita. genial.. genial.. genial.. !!!!
me envia un mensaje de error y es el siguiente
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.
gracias espero puedan ayudarme
gente muy bueno y anda muy bien. pero queria saber si se puede modificar para que tome un usaurio logueado en una aplicacion de vb.net. ees para un trabajo de lafacu, y necesito auditar el usuario logueado y no el de la maquina.
Gracias; Muy buen trabajo con este SCRIPT
gracias me ahorro mucho trabajo el cres es excelente tu aporte me gustaria saber si tengo dos base de datos uno llamado SAS y el otro Auditoria y quiero enviar a AUDITORIA LOS CAMBIOS EN LAS tablas de produccion SAS que vario en el script gracias por tu tiempo
Mucha, gracias, excelente
Por lo que he leido es muy bueno. Soy relativamente nueva en SQL que debo hacer para ponerlo en mi bd.
mejorando el script->
– Corregidos los errores del script
– agrupando algunas consultas para extraer datos:
USE MYDATABASE–Ponerle el nombre de la base de datos que va hacer auditada
GO
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Audit’)
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
WHILE @TABLE_NAME IS NOT NULL
BEGIN
EXEC(‘IF OBJECT_ID (»’ + @TABLE_NAME+ ‘_ChangeTracking», »TR») IS NOT NULL DROP TRIGGER ‘ + @TABLE_NAME+ ‘_ChangeTracking’)
SELECT @sql =
‘
create trigger ‘ + @TABLE_NAME+ ‘_ChangeTracking on ‘ + @TABLE_NAME+ ‘ for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = »’ + @TABLE_NAME+ »’
— date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + » » + convert(varchar(12), getdate(), 114)
— Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = »U»
else
select @Type = »I»
else
select @Type = »D»
— get list of columns
select * into #ins from inserted
select * into #del from deleted
— Get primary key columns for full outer join
select
@PKCols = coalesce(@PKCols + » and», » on») + » i.» + c.COLUMN_NAME + » = d.» + c.COLUMN_NAME,
— Get primary key fields select for insert
@PKFieldSelect = coalesce(@PKFieldSelect+»+»,»») + »»»» + COLUMN_NAME + »»»»,
@PKValueSelect = coalesce(@PKValueSelect+»+»,»») + »convert(varchar(100), coalesce(i.» + COLUMN_NAME + »,d.» + 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
if @PKCols is null
begin
raiserror(»no PK on table %s», 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
group by ORDINAL_POSITION,COLUMN_NAME
order by ORDINAL_POSITION desc
while @field < @maxfield begin select @bit = (@field - 1 )% 8 + 1 select @bit = power(2,@bit - 1) select @char = ((@field - 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (»I»,»D»)
begin
select @sql = »insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)»
select @sql = @sql + » select »»» + @Type + »»»»
select @sql = @sql + »,»»» + @TableName + »»»»
select @sql = @sql + »,» + @PKFieldSelect
select @sql = @sql + »,» + @PKValueSelect
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
‘
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
END
Este otro es para aquellos que necesitan que el registro de las auditorias este en una sola tupla. Es decir que todos los campos afectados por alguna de las operaciones (insert, update, delete) estarán concatenados y separados por ‘|’
USE MYDATABASE–Ponerle el nombre de la base de datos que va hacer auditada
GO
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Audit’)
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(1000),
OldValue varchar(2000),
NewValue varchar(2000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
WHILE @TABLE_NAME IS NOT NULL
BEGIN
EXEC(‘IF OBJECT_ID (»TR_AUDIT_’ + @TABLE_NAME + »’, »TR») IS NOT NULL DROP TRIGGER TR_AUDIT_’ + @TABLE_NAME+ »)
SELECT @sql =
‘
create trigger TR_AUDIT_’ + @TABLE_NAME + ‘ on ‘ + @TABLE_NAME+ ‘ for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(64) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(8000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000),
@fieldsNames varchar(1000) ,
@ValuesOld varchar(2000),
@ValuesNew varchar(2000)
select @TableName = »’ + @TABLE_NAME+ »’
— date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + » » + convert(varchar(12), getdate(), 114)
— Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = »U»
else
select @Type = »I»
else
select @Type = »D»
— get list of columns
select * into #ins from inserted
select * into #del from deleted
— Get primary key columns for full outer join
select
@PKCols = coalesce(@PKCols + » and», » on») + » i.» + c.COLUMN_NAME + » = d.» + c.COLUMN_NAME,
@PKFieldSelect = coalesce(@PKFieldSelect+»+»»|»»+»,»») + »»»» + COLUMN_NAME + »»»»,
@PKValueSelect = coalesce(@PKValueSelect+»+»»|»»+»,»») + »convert(varchar(1000), coalesce(i.» + COLUMN_NAME + »,d.» + 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
if @PKCols is null
begin
raiserror(»no PK on table %s», 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield begin select @field = min(ORDINAL_POSITION),@fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
group by ORDINAL_POSITION,COLUMN_NAME
order by ORDINAL_POSITION desc
select @bit = (@field – 1 )% 8 + 1
select @bit = power(2,@bit – 1)
select @char = ((@field – 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (»I»,»D»)
begin
select
@fieldsNames = coalesce(@fieldsNames +»+»»|»»+»,»», »») + »»»» + COLUMN_NAME + »»»»,
@ValuesOld = coalesce(@ValuesOld +»+»»|»»+»,»», »») + »convert(varchar(2000), coalesce(d.» + c.COLUMN_NAME + »,»»»»))»,
@ValuesNew = coalesce(@ValuesNew +»+»»|»»+»,»», »») + »convert(varchar(2000), coalesce(i.» + c.COLUMN_NAME + »,»»»»))»
from
INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @TableName
and c.COLUMN_NAME=@fieldname
end
end
select @sql = »insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)»
select @sql = @sql + » select »»» + @Type + »»»»
select @sql = @sql + »,»»» + @TableName + »»»»
select @sql = @sql + »,» + @PKFieldSelect
select @sql = @sql + »,» + @PKValueSelect
select @sql = @sql + »,» + @fieldsNames
select @sql = @sql + »,» + @ValuesOld
select @sql = @sql + »,» + @ValuesNew
select @sql = @sql + »,»»» + @UpdateDate + »»»»
select @sql = @sql + »,»»» + @UserName + »»»»
select @sql = @sql + » from #ins i full outer join #del d »
select @sql = @sql + @PKCols
exec (@sql)
‘
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= ‘BASE TABLE’
AND TABLE_NAME!= ‘sysdiagrams’
AND TABLE_NAME!= ‘Audit’
END
Excelente aporte, yo modifique un poco el trigger para que funcione para n capas, pero se basa en que cualquier tabla del sistema tenga un campo aud_usuario, para que este decienda desde la capa web hasta el trigger de la base de datos, En cuanto al ultimo post de Alexander Cuellar Jimenez en que dice para que salga concatenado me parece chevere, pero hay un detalle que modificar si actualizas tres campos se hace tres inserts en la base la tabla de audit, y bastaría con uno pues los campos y valores ya estan concatenados
muy bueno felicitaciones ……
Hola buenas noches,
Yo quisiera saber como puedo cambiar
@UserName = system_user
por el usuario que inicio sesion que no esta logueado a nivel de base de datos, sino que esta almacenado en una tabla usuario.
Saludos.
Hola Esteban,
Soy nueva en estos medios y el concepto es excelente, pero, que puedo hacer si mis tablas no contienen llave primaria ?
saludos!