Podría haber un spr oculto?

Como estas revisa los eventos del Visor de sucesos puede que hay te de un poco de información otro podría ser que estén haciendo un link server desde otra bd a la tuya y hacen esas modificaciones o algún usuario en especifico, también puedes ver el monitor de actividades del sql otra opción que te doy es que audites tus tablas así podrás ver que usuario esta afectando tus tablas y a que hora aquí te dejo el script por si te sirve:

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 nvarchar(128),
primarykeyfield nvarchar(1000),
primarykeyvalue nvarchar(1000),
fieldname nvarchar(128),
oldvalue nvarchar(1000),
newvalue nvarchar(1000),
updatedate nvarchar default (getdate()),
username nvarchar(128)
)
Go
Declare @sql1 varchar(8000), @sql2 varchar(8000), @sql3 varchar(8000),@table_name sysname, @compatible_triggerfields varchar(8000)
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'
and table_name != 'dtproperties'
while @table_name is not null
begin
exec('if object_id (''' + @table_name+ '_changetracking'', ''tr'') is not null drop trigger ' + @table_name+ '_changetracking')
set @compatible_triggerfields = null
select @compatible_triggerfields = coalesce(@compatible_triggerfields+'],[','[') + column_name from information_schema.columns where table_name = @table_name and data_type not in ('text','ntext','image')
set @compatible_triggerfields = @compatible_triggerfields + ']'
select @sql1 =
'
--------------------------------------------
-- --
-- '+ @table_name +'
-- --
--------------------------------------------
create trigger [dbo].[' + @table_name+ '_changetracking] on [dbo].[' + @table_name+ '] for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(64),
@tablename varchar(64),
@pkcols varchar(1000),
@sql varchar(2000),
@updatedate varchar(21),
@username varchar(64),
@type char(1) ,
@pkselect varchar(1000),
@oldvaluesql varchar(128),
@newvaluesql varchar(128),
@fieldtype varchar(32),
@compatible_triggerfield bit
--you will need to change @tablename to match the table to be audited
select @tablename = ''' + @table_name+ '''
-- date and user
select @username = system_user , @updatedate = convert(varchar(8), getdate(), 112)
+ '' '' + convert(varchar(12), getdate(), 114)'
select @sql2 = '
-- 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 '+ @compatible_triggerfields +' into #ins from inserted
select '+ @compatible_triggerfields +' into #del from deleted
'
select @sql3 = '-- get primary key columns for full outer join
select @pkcols = coalesce(@pkcols + '' and'', '' on'')
+ '' i.'' + cu.column_name + '' = d.'' + cu.column_name
from information_schema.table_constraints porque ,
information_schema.key_column_usage cu
where porque.table_name = @tablename
and constraint_type = ''primary key''
and cu.table_name = porque.table_name
and cu.constraint_name = porque.constraint_name
-- get primary key select for insert
select @pkselect = coalesce(@pkselect+''+'','''')
+ ''''''<'' + column_name
+ ''=''''+convert(varchar(100), coalesce(i.'' + column_name +'',d.'' + column_name + ''))+''''>''''''
from information_schema.table_constraints porque ,
information_schema.key_column_usage cu
where pk.table_name = @tablename
and constraint_type = ''primary key''
and cu.table_name = pk.table_name
and cu.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, @fieldtype = data_type
from information_schema.columns
where table_name = @tablename
and ordinal_position = @field
if @fieldtype = ''text'' or @fieldtype = ''ntext'' or @fieldtype = ''image'' set @compatible_triggerfield = 0 else set @compatible_triggerfield = 1
set @oldvaluesql = '',convert(varchar(1000),d.'' + @fieldname + '')''
set @newvaluesql = '',convert(varchar(1000),i.'' + @fieldname + '')''
if @compatible_triggerfield = 0
begin
set @oldvaluesql = '',''''n/a''''''
set @newvaluesql = '',''''n/a''''''
end
select @sql = ''
insert audit ( type,
tablename,
primarykeyfield,
fieldname,
fieldtype,
oldvalue,
newvalue,
updatedate,
username)
select '''''' + @type + '''''',''''''
+ @tablename + '''''','' + @pkselect
+ '','''''' + @fieldname + ''''''''
+ '','''''' + @fieldtype + ''''''''
+ @oldvaluesql
+ @newvaluesql
+ '','''''' + @updatedate + ''''''''
+ '','''''' + @username + ''''''''
+ '' from #ins i full outer join #del d''
+ @pkcols
if @compatible_triggerfield = 1
begin
set @sql = @sql
+ '' where i.'' + @fieldname + '' <> d.'' + @fieldname
+ '' or (i.'' + @fieldname + '' is null and d.''
+ @fieldname
+ '' is not null)''
+ '' or (i.'' + @fieldname + '' is not null and d.''
+ @fieldname
+ '' is null)''
end
exec (@sql)
--execute spwritestringtofile @sql,''c:\'',''test.txt''
end
end
'
exec(@sql1+@sql2+@sql3);
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'
and table_name != 'dtproperties'
end

Cualquier duda me consultas

gracias por el haber contestado.

pues empiezo a checar, y si tengo alguna duda, no dudes que te lo haré saber

mil gracias!!!!!

buenos días.

pues con la molestia, copie el código que pusiste y ejecute en el query analyzer, pero manda el siguiente error.

Server: Msg 107, Level 16, State 2, Procedure AcuerdoCliente_changetracking, Line 51
The column prefix 'pk ' does not match with a table name or alias name used in the query.

que debo de hacer por este error?.

gracias!!!!

pd. lo que esta en negritas es sin el espacio, pero tuve que ponerlo porque ponía la palabra porque en vez de lo que esta en negritas.

Disculpa este sino te dará error revisa las lineas 41 y 237 hay que agregar las tablas que deseamos auditar tiene un comentario -- nombre de las tablas a auditar :

if not exists(select * from information_schema.tables where table_name= 'audit')
Begin
--Ojo revisar lineas 41 y 237 hay que agregar las tablas que deseamos auditar
create table audit
(
auditid [int]identity(1,1) not null,
type char(1),
tablename nvarchar(250),
primarykeyfield nvarchar(max),
primarykeyvalue nvarchar(max),
fieldname nvarchar(max),
oldvalue nvarchar(max),
newvalue nvarchar(max),
updatedate smalldatetime,
username nvarchar(max)
) on [primary]
create nonclustered index idxaudit1 on audit( [type], [tablename], [updatedate] )
create nonclustered index idxaudit2 on audit( [tablename], [type], [updatedate] )
end
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 in ('','','',....) -- nombre de las tablas a auditar
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
'
--print @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 in ('','','',....) -- nombre de las tablas a auditar
end

buenas tardes.

pues con la novedad que ya esta trabajando en una bd local el script registrando todas las modificaciones que se hacen a las tablas a auditar, desde programa o desde el Enterprise Manager, y eso esta excelente, pero tengo las siguientes preguntas:

- como poner en el scrpit la instrucción HOST_NAME(), ya que esta me graba en tabla (normalmente ventas y cxc) la maquina desde donde se ejecuta cualquier insert, update, etc. el programa esta en VB6, pero se accesa con un único usuario la bd, pero para entrar al programa, se entra por usuario dado de alta en el catalogo de empleados y que trabaje en los módulos a los que esta asignado (el usuario esta definido en un archivo config.ini para accesar la bd) . NO ME EXPLIQUE, ESPERO QUE SI

- es conveniente borrar de la tabla AUDIT, los registros de meses anteriores, desde el momento en que empiece a funcionar el script, para que no vaya a crecer la bd? respladar en un EXCEL los movimientos borrados

gracias por tu interés por ayudarme, y espero sea lo ultimo en que te moleste.

mil gracias!!!

¿Respondiendo a tu pregunta el HOST_NAME() lo guardas en la BD o todavía no lo hace? Ya que por lo que entiendo se accesa con un solo usuario de la BD pero con lo que lo diferencia usuario dado de alta el cual me imagino que guardas en una variable la sesión de dichos usuarios, si es así lo que tendrías que hacer es pasar esa variable de la sesión en tus consultas de insert, delete, update y hay si se podría modificar el script que te pase otro consejo que te doy es que también al iniciar la sesión de dicho usuario atrapes la ip de la maquina si es que se puede accesar desde cualquier maquina ya que esto se presta a personas que entres con usuarios de otros, por otra parte la tabla audit por ningún motivo la borres ya que esto te va a servir de respaldo para futuras modificaciones que no puedas respaldar en el momento o que borren de tus respaldo esta tabla es el eje fundamental para auditar te lo digo por que estoy en el área de aseguramiento y esta tabla me a salvado en mas de una ocasión solo si estas seguro puedes hacer el borrado y respaldar en un excel.

gracias por seguir apoyándome en este asunto que, gracias a ti, prácticamente ya esta resuelto.

como bien comentas, hay usuario único para conectarse a la base de datos por medio del archivo config.ini, pero al ejecutar los programas hechos en VB6, se pide el usuario y contraseña para entrar a estos, y poder trabajar según los permisos de acceso a los módulos de los programa. actualmante se graba el nombre del equipo donde se ejecutan altas de registros en las tablas de ventas y cxc, ya que en algunos procedimientos almacenados en la base de datos se usa esta instrucción (HOST_NAME), en esas tablas solo se guarda el nombre de la maquina que agrega registros.

el caso que me comentas, en que parte del script se debe de corregir y poner el HOST_NAME para guardar el nombre del equipo en la tabla AUDIT

nuevamente, mil gracias por tu apoyo y comprensión.
saludos!!!

buenas tardes.

pues estuve haciendo pruebas para poner la instrucccion HOST_NAME en el script que amablemente publicaste en mi pregunta. hice la siguiente modificación
cambie
select @sql = @sql + '','''''' + @username + ''''''''
por
select @sql = @sql + '',host_name()''

estoy haciendo pruebas en forma local y parece que si funciona, espero me confirmes si esta correcto lo que hice.

otra pregunta mas, espero sea la ultima, si quiero poner este trigger a otra tabla(es), solo pongo el nombre de la(es) tabla(es) en el script y ejecuto.

gracias por tu apoyo e interés

saludos!!!