Array
(
)

trigger dinâmica para tabela de log

Acacio
   - 05 set 2007

Estou fazendo uma trigger dinâmica para alimentar tabela de log. Já consegui pegar todos os campos da tabela e jogar numa variável. Só não consegui pegar os valores dos campos das propriedades ´new´ e ´old´ dinamicamente e jogar junto com os nomes dos campos.

exemplo

em vez de pegar o campo new.i_codigo
pegar dinamicamente new.field[1] que corresponderia ao ´i_codigo´
e jogá-lo numa variável qualquer.

Abaixo fiz o exemplo de como estou fazendo. Já traz todos os campos,mas não
consigo jogar os valores para a variável correspondente. Acho que falta
pouco para consiguir fazer uma trigger de log dinâmica.

********************************************************************************

create table Teste(
id_codigo integer not null primary key,
c_nome varchar(50),
c_nome2 varchar(50))

create table Tab_log (
id_codigo integer not null primary key,
c_sql varchar(1000))

alter TRIGGER tr_Teste4 before update on DBA.Teste
referencing old as id_codigo new as new_Codigo
FOR EACH ROW
BEGIN
declare @listaCampos varchar(50);
declare @lista_Nome_Tabela varchar(100);
declare @lista_SQL_variáveis varchar(2000);
declare @Contador integer;
declare @ind integer;
declare c_lote2 dynamic scroll cursor for select count(COL.table_id) from SYSCOLUMN as COL where
COL.TABLE_ID = 768;//id_tabela;
declare cursor_atualizados scroll cursor for select COL.COLUMN_NAME,tab.table_name from SysTable tab
join SYSCOLUMN COL on tab.table_id = COL.table_id
where COL.TABLE_ID = 768;
open c_lote2;
fetch next c_lote2 into @Contador;
close c_lote2;
//select COL.COLUMN_NAME from SYSCOLUMN as COL where COL.TABLE_ID = 482
-- abre o cursor
open cursor_atualizados;
-- pega o primeiro registro
fetch next cursor_atualizados
into @listaCampos,@lista_Nome_Tabela; -- deve ser igual a lista de campos
-- esse LOOP fará a manipulação de todos os registros
-- que sofreram alteração. ele irá executar enquanto seja
-- possível passar para um próximo registro do cursor
set @lista_SQL_variáveis = ´update ´ || @lista_Nome_Tabela || ´ ´ || @listaCampos || ´ = ´; //new_Codigo.
set @ind = 1;
//LoopGetRow:
//LOOP
while @ind < @Contador LOOP
// begin//@@FETCH_STATUS = 0
-- ***********************************
-- aqui vc deve gerar o seu log,
-- trabalhando sempre com as variáveis
-- ***********************************
-- pega o próximo registro
set @lista_SQL_variáveis = @lista_SQL_variáveis || ´ ´ || @listaCampos || ´ = ´;// || ´ ´ || ´ ´ || @lista_de_variáveis;//new_Codigo || ´.´ ||
fetch next cursor_atualizados
into @listaCampos,@lista_Nome_Tabela;
set @ind = @ind + 1;
//if @ind = 3 then
// LEAVE LoopGetRow;
END LOOP;//LOOP LoopGetRow;
-- fecha o cursor
insert into Tab_log (id_codigo,c_sql) values (1,@lista_SQL_variáveis);
close cursor_atualizados
end

update Teste set c_nome = ´Teste 1´,
c_nome2 = ´Teste 3333´
where id_codigo = 2;
select * from Tab_log
commit;

Tecnicon
   - 20 abr 2012

Conseguiu encontrar uma solução?

Dentro de um Trigger podemos utilizar a tabela temporária INSERTED e DELETED (que o SQL Server cria somente durante o Trigger) para visualizarmos os dados que estão sendo inseridos (onde somente a tabela inserted terá registros), alterados (a tabela insertes tem os novos dados e a tabela delete possui os dados antigos) ou deletados(onde somente a tabela deleted terá registros).

Estou tentando montar uma Trigger Dinâmica, mas também não consigo executar o SQL montado dinamincamente.
CREATE TRIGGER [dbo].[T_PEDIDOS]
ON [dbo].[Pedidos]
AFTER INSERT,UPDATE,DELETE
AS
DECLARE
@TABELA varchar(50)=Pedidos,
@COLUNA varchar(50),
@TIPO varchar(20),
@OP int,--ORDINAL POSITION
@PK varchar(50),--PRIMARY KEY
@TC int=0,--TOTAL DE COLUNAS
@C int=1,--CONTADOR
@SQL nvarchar(max),
@OLD varchar(MAX),
@NEW varchar(MAX)
BEGIN
SET NOCOUNT ON;
--TOTAL DE COLUNAS
SELECT @TC=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS Field WHERE TABLE_NAME = @TABELA;
--QUEM É A CHAVE PRIMARIA
SELECT @PK=COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME =@TABELA AND CONSTRAINT_TYPE=PRIMARY KEY);
--QUAL A ORDINAL_POSITION DA PK
SELECT @OP=ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=@PK AND TABLE_NAME=@TABELA;

--LOOP NAS COLUNAS DA TABELA
WHILE (@C<=@TC)
BEGIN
--SE FOR DIFERENTE DA PRIMARY KEY
IF @C@OP
BEGIN
SET @NEW=null;
SET @OLD=null;
SELECT @COLUNA=COLUMN_NAME, @TIPO=DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABELA AND ORDINAL_POSITION=@C;
SET @SQL=SELECT @NEW=+@COLUNA+ FROM inserted;
EXEC @SQL; --aqui ocorre o erro Could not find stored procedure SELECT @NEW=Produto FROM inserted.
END
SET @C=@C+1;
END
END
GO