Tablas deleted e inserted en código genérico

Hola Carlos,

Una forma que he visto, es usnado la clausula "output" en las sentencia DML,
donde puedes referenciar ambas tablas [deleted] e [inserted].

Si tienes acceso a SS 2008, entonces puedieras usar las nuevas
funcionalidades ""Change Tracking" y "Change Data Capture".


AMB


"Carlos M. Calvelo" wrote:

Hola a todos,

Si en un trigger se quiere hacer algo con sql dinámico o pasar
control a una función o procedimiento almacenado, las tablas
deleted e inserted ya no son accesibles.

La única forma que se me ocurre es hacer una copia de las tablas
(tablas temporales o variables) y utilizar las copias.

Concretamente, en el trigger, hacer algo como:

-
select * into #del from deleted
select * into #ins from inserted


drop table #ins
drop table #del
-

Pero no me gusta esto. Con una operación sobre una tabla lo
suficientemente grande no me parece una buena idea.

Se trata de funcionalidad genérica para registrar todos los cambios
en una u otra tabla. La cosa ya funciona (SS2005) pero este aspecto
no me gusta mucho. Obviamente la idea de simplemente repetir el
código genérico en los triggers donde sea necesario tampoco es muy
atractiva (mantenimiento). Vendría bien para esto algo como macros.
Quizás un procedimiento que genere el código en los triggers.
Hmmm.. es posible.

Pues bien. Es posible de alguna manera utilizar directamente las
tablas inserted y deleted (evitando así hacer copias) en este tipo
de situaciones? Algún otro truco o idea?

Saludos,
Carlos

Hola Alejandro,

On 9 okt, 01:53, Alejandro Mesa
wrote:

Hola Carlos,

Una forma que he visto, es usnado la clausula "output" en las sentencia DML,
donde puedes referenciar ambas tablas [deleted] e [inserted].

Aunque nunca es utilizado esta opcion, entiendo que es entonces
responsabilidad del que ejecuta las operaciones hacer algo con
esas tablas. El programador, el que ejecuta ad-hoc queries, etc.
En ese caso SS no puede garantizar que 'siempre' se hará lo que
hay que hacer.

Además si especificamos un OUTPUT deleted.*, inserted.*,
necesitaría código especifico para cada tabla, y el resultado
del OUTPUT ya es una copia no? O lo tengo que pasar
de alguna forma a un procedimiento, vamos... copiar otra vez, que
es lo que estoy tratando de evitar.

Ahora el código es el mismo para todas las tablas, basado en
el information_schema.

Si tienes acceso a SS 2008, entonces puedieras usar las nuevas
funcionalidades ""Change Tracking" y "Change Data Capture".

Ya había estado mirando :) De momento SS2008 no es una
opción.

Gracias por tu ayuda y saludos,
Carlos

Carlos,

Por lo que ahora entiendo, buscas una forma general de poder guardar esa
informacion, independiente de la tabla donde ocurra. Seguro se puede hacer,
quizas usando sql dinamico como comentastes en un principio, y una tabla
generica donde puedas almacenar el nombre / id de la tabla, nombre / id de la
columna, tipo de data, valor (sql_variant quizas), id del usuario, etc. La
idea seria algo asi como el modelo EAV (entity attribute value).

Ahora que tal sera el desempenio, no se, pero me inmagino que en dependencia
de el tiempo de respuesta que necesites en tu aplicacion cliente, asi sera el
nivel de cambios que tendras que guardar para que el desempenio no se vea muy
afectado.

Tendrias que valorar las prioridades de tu sistema / aplicacion, desempenio
contra auditoria.

Si acaso se te alumbra el bombillo, comparte la idea con nosotros. Estoy
segiro que mas de uno ha necesitado hacer esto.

Las veces que lo he hecho, he terminado por crear una tabla de auditoria por
cada tabla que deseo chequear. Esto no es muy amigable que digamos, pues si
maniana introducimos / quitamos una columna, mas de un codigo tendra que ser
modificado.

AMB
"Carlos M. Calvelo" wrote:

Hola Alejandro,

On 9 okt, 01:53, Alejandro Mesa
wrote:
> Hola Carlos,
>
> Una forma que he visto, es usnado la clausula "output" en las sentencia DML,
> donde puedes referenciar ambas tablas [deleted] e [inserted].

Aunque nunca es utilizado esta opcion, entiendo que es entonces
responsabilidad del que ejecuta las operaciones hacer algo con
esas tablas. El programador, el que ejecuta ad-hoc queries, etc.
En ese caso SS no puede garantizar que 'siempre' se hará lo que
hay que hacer.

Además si especificamos un OUTPUT deleted.*, inserted.*,
necesitaría código especifico para cada tabla, y el resultado
del OUTPUT ya es una copia no? O lo tengo que pasar
de alguna forma a un procedimiento, vamos... copiar otra vez, que
es lo que estoy tratando de evitar.

Ahora el código es el mismo para todas las tablas, basado en
el information_schema.


>
> Si tienes acceso a SS 2008, entonces puedieras usar las nuevas
> funcionalidades ""Change Tracking" y "Change Data Capture".
>

Ya había estado mirando :) De momento SS2008 no es una
opción.

Gracias por tu ayuda y saludos,
Carlos

Hola Alejandro,

On 9 okt, 14:52, Alejandro Mesa
wrote:

Carlos,

Por lo que ahora entiendo, buscas una forma general de poder guardar esa
informacion, independiente de la tabla donde ocurra.

Bueno... solo para las tablas donde en un update trigger se
llame ese procedimiento genérico.

Seguro se puede hacer,

Como que lo estoy haciendo! :-)

quizas usando sql dinamico como comentastes en un principio, y una tabla
generica donde puedas almacenar el nombre / id de la tabla, nombre / id de la
columna, tipo de data, valor (sql_variant quizas), id del usuario, etc. La
idea seria algo asi como el modelo EAV (entity attribute value).

He visto ejemplos de esta idea. Pero trato de evitar todo lo que
tenga que ver con el modelo EAV (me parece una barbaridad).
Lo que yo estoy haciendo es que para cada tabla, digamos TablaA, se
crea un tabla 'History': TablaAHistory. Aparte de un par de columnas
extra en la tabla History, las dos tienen la misma estructura.
Eso simplifica las consultas sobre la tabla History. Una desventaja
es que se repite todo un registro en la tabla History aunque solo
haya sido actualizada una columna.

Ahora que tal sera el desempenio, no se, pero me inmagino que en dependencia
de el tiempo de respuesta que necesites en tu aplicacion cliente, asi sera el
nivel de cambios que tendras que guardar para que el desempenio no se vea muy
afectado.

Tendrias que valorar las prioridades de tu sistema / aplicacion, desempenio
contra auditoria.

Si acaso se te alumbra el bombillo, comparte la idea con nosotros. Estoy
segiro que mas de uno ha necesitado hacer esto.

Todavía no está completo pero ya se puede probar y lo pongo
aquí abajo.

Las veces que lo he hecho, he terminado por crear una tabla de auditoria por
cada tabla que deseo chequear. Esto no es muy amigable que digamos, pues si
maniana introducimos / quitamos una columna, mas de un codigo tendra que ser
modificado.

Esa parte también trato de automatizarla.

Vamos a ver... todo empieza con que en los update trigges se tiene
que determinar si algo ha cambiado. Entoces tienes que determinar
para cada columna si acepta nulos o no y comparar los registros
en deleted con inserted. Para columnas que aceptan nulos haríamos
algo como (d es el alias de deleted e i de inserted):

d.columna is null and i.columna is not null or
d.columna is not null and i.columna is null or
d.columna <> i.columna

Y para las demás columnas solo:

d.columna <> i.columna.

Si se tienen varias tablas para las que se quieren registrar los
cambios, estas tablas tienen muchas columnas y se está cambiando
la estructura debido a desarrollo o mantenimiento, entonces
está claro que ese método no va muy lejos :) Además toda la
información necesaria para automatizarlo está en el information_schema!

Lo que tengo por ahora funciona con las siguientes restricciones:
(en una versión definitiva no las tendríamos)

- Cada tabla para la que queremos mantener una 'History' tiene
que tener un PK con solo una columna y su nombre tiene que
ser <nombre de la tabla>ID. Por ejemplo tabla Tab tiene que
tener una columna TabID que es el PK.

- El nombre de la tabla 'History' es <nombre de la tabla>History
Por ejempo para tabla Tab es TabHistory.

La tabla TabHistory tendrá la misma estructura que Tab, mas un
par de columnas: TabHistoryID (PK), ChangedOn y ChangedBy

Bien... aquí está el procedimiento 'UpdateHistory'. Lo podemos
probar todo en tempdb

Update history tiene dos partes:
La primera trata hacer un create o alter si ha cambiado algo el
la tabla original. Esta parte se pondrá en otro procedimiento y
se ejecutará solo cuando se haga algún cambio en la tabla original
y no siempre desde los triggers. Ahora está ahí solo para hacer
pruebas facilmente.
La segunda parte (con el comentario '-- update History') es la que
determina si hay cambios y los registra.

use tempdb
go

create procedure UpdateHistory (@table_name sysname)
as
begin
declare @table_columns nvarchar(max)
declare @where_clause nvarchar(max)
declare @sqlsentence nvarchar(max)
declare @table_pk_column sysname
declare @history_table_name sysname
declare @history_table_pk_column sysname

set @table_pk_column = @table_name + 'ID'
set @history_table_name = @table_name + 'History'
set @history_table_pk_column = @history_table_name + 'ID'

if object_id('dbo.'+@history_table_name) is null
begin
set @sqlsentence N'create table ' + @history_table_name + N' (' +
@history_table_pk_column + N' int not null identity primary
key,'+
N'ChangedOn datetime not null default getdate(),'+
N'ChangedBy varchar(128) not null default system_user'

select @sqlsentence = @sqlsentence+ N',' + column_name + N' ' +
data_type +
case when data_type in
('binary','char','nchar','nvarchar','varbinary','varchar')
then '('+case character_maximum_length when -1 then 'max'
else cast(character_maximum_length as varchar) end +')'
when data_type in ('decimal','numeric')
then '('+cast(numeric_precision as
varchar)+','+cast(numeric_scale as varchar)+')'
else ''
end
from information_schema.columns
where table_name=@table_name
order by ordinal_position

set @sqlsentence = @sqlsentence+')'
print 'UpdateHistory: ' + @sqlsentence

exec sp_executesql @sqlsentence
end
else
begin
set @sqlsentence=N''
select @sqlsentence = @sqlsentence +
case @sqlsentence when '' then '' else ',' end + T.column_name
+ ' ' + data_type +
case when data_type in
('binary','char','nchar','nvarchar','varbinary','varchar')
then '('+case character_maximum_length when -1 then 'max'
else cast(character_maximum_length as varchar) end +')'
when data_type in ('decimal','numeric')
then '('+cast(numeric_precision as
varchar)+','+cast(numeric_scale as varchar)+')'
else ''
end

from information_schema.columns T
where table_name=@table_name
and not exists (select *
from information_schema.columns
where table_name=@history_table_name and
column_name = T.column_name)

if @sqlsentence <> N''
begin
set @sqlsentence = N'alter table ' + @history_table_name + N'
add ' + @sqlsentence
print 'UpdateHistory: ' +@sqlsentence

exec sp_executesql @sqlsentence
end
end


set @table_columns = ''
set @where_clause = ''
select @table_columns = @table_columns +
case @table_columns when '' then '' else ',' end +
'd.'+column_name,
@where_clause = @where_clause +
case @where_clause when '' then '' else ' or ' end +
case is_nullable
when 'YES' then 'd.'+column_name+' is null and
i.'+column_name+' is not null or '+
'd.'+column_name+' is not null and
i.'+column_name+' is null or '
else ''
end + 'd.'+column_name+'<>i.'+column_name
from information_schema.columns
where table_name=@table_name
order by ordinal_position

set @sqlsentence =
N'insert ' + @history_table_name + N' (' + @table_columns + ')' +
N' select ' + @table_columns +
N' from #del d join #ins i on d.' + @table_pk_column + N'=i.' +
@table_pk_column +
N' where ' + @where_clause

print 'UpdateHistory: '+@sqlsentence

exec sp_executesql @sqlsentence
end
go


create table Tab
(
TabID int not null identity primary key,
Status char(20) not null ,
Description varchar(50) not null,
AnInteger int,
)
go


create trigger Trig_Tab_upd1 on Tab after update as
begin

declare @rc int
set @rc = @@rowcount

set nocount on

if @rc = 0
begin
print 'Trig_Tab_upd1: @rc = 0. -> return'
return
end

if update(TabID) -- Can't update TabID
begin
print 'Trig_Tab_upd1: Update TabID(PK) not allowed. -> return'
return
end

print 'Trig_Tab_upd1: UpdateHistory'

select * into #del from deleted
select * into #ins from inserted
exec dbo.UpdateHistory 'Tab';
drop table #ins
drop table #del
end
go



insert Tab (Status,Description,AnInteger)
values (1,'Descr 1', 1)
insert Tab (Status,Description,AnInteger)
values (1,'Descr 2', 2)
go

update Tab set AnInteger=3 where TabID=2
go

select * from Tab
select * from TabHistory
go



EXECUTE sp_rename N'dbo.Tab.AnInteger', N'Tmp_AnInteger2_6', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.Tab.Tmp_AnInteger2_6', N'AnInteger2', 'COLUMN'
GO


ALTER TABLE dbo.Tab ADD
ADatetime datetime NULL
GO


update Tab set ADatetime=getdate() where TabID=2
go


select * from Tab
select * from TabHistory

drop table TabHistory
drop table Tab
drop proc UpdateHistory

Bien. Ahora vuelvo a la pregunta orignal. :-)
Como puedo evitar el tener que hacer copias de las tablas
deleted e inserted?

En concreto en el trigger tengo esto:

select * into #del from deleted
select * into #ins from inserted
exec dbo.UpdateHistory 'Tab';
drop table #ins
drop table #del

porque no puedo utilizar delete e inserted en sql dinámico o en
otro procedimineto como dbo.UpdateHistory.

La única forma que me puedo imaginar es generación de código
en el trigger. O sea, que lo que hago con sql dinámico en
el procedimiento, tendría que ser generado en el trigger cada
vez que cambia algo en la estructura de la tabla.

Saludos,
Carlos