Passando valores de uma procedure para uma trigger utilizando o CONTEXT_INFO do SQL


Em alguns contextos, por exemplo, auditoria de exclusão de registro, pode ser necessário ter que utilizar parâmetros que foram passados para uma procedure em uma trigger que seria responsável por fazer a auditoria da exclusão.

Porém como fazer para que o parâmetro passado para a procedure chegue até a trigger?

Aí entra uma função chamada CONTEXT_INFO. Essa função insere informações binárias na sessão atual. A sintaxe do comando é a seguinte:

SET CONTEXT_INFO { binary_str | @binary_var }

Vou dar um exemplo de como utilizar essa função em uma tabela de auditoria de itens removidos de uma tabela. Suponhamos que temos duas tabela no sistema. Uma chamada Teste e uma que armazenará os dados excluídos da tabela Teste onde ficará armazenado quem removeu o registro e o motivo.

create table Teste
(
       IDTeste int identity(1,1) not null,
       Descricao varchar(50) null,
       CriadoPor int null
)

create table DeletedTeste
(
       IDDeletedTeste int identity(1,1) not null,
       IDTeste int null,
       Descricao varchar(50) null,
       Motivo varchar(100) null,
       ExcluidoPor int null,
       DataExclusao smalldatetime null
)

Para remover um registro da tabela Teste o usuário deverá utilizar uma procedure, onde informa qual o registro que deve ser excluído, quem está fazendo a exclusão e qual o motivo dessa exclusão.

A procedure deverá ser como a seguinte:

create procedure up_DeleteTeste
       @IDTeste int,
       @IDUsuario int,
       @Motivo varchar(100)
as
begin

       — declaracoes para o CONTEXT_INFO
       declare @xml xml, @varXML varbinary(128)

       — dados e conversao para o CONTEXT_INFO
       select @xml = (select @IDUsuario as ‘@IDUsuario’, @Motivo as ‘@Motivo’ for xml path(‘Values’))
       select @varXML = cast(cast(@xml as varchar(max)) as varbinary(128))

      — insercao dos dados no CONTEXT_INFO
       set context_info @varXML

       — remocao do registro da tabela Teste
       delete
       from   Teste
       where  IDTeste = @IDTeste

end
go

Antes de apresentar a trigger que faz a auditoria, vamos explicar como utilizar a função CONTEXT_INFO.

Primeiro, criamos uma instrução XML para armazenar os dois dados que a trigger precisará, no nosso caso, ID do usuário que está excluíndo e o motivo.

select @xml = (select @IDUsuario as ‘@IDUsuario’, @Motivo as ‘@Motivo’ for xml path(‘Values’))

Depois, convertemos esse XML em uma variável do tipo VARBINARY(128) e inserimos no CONTEXT_INFO.

select @varXML = cast(cast(@xml as varchar(max)) as varbinary(128))
set context_info @varXML

Agora vamos a trigger que fará a auditoria:

alter trigger trg_Teste_DEL on Teste
after delete
as
begin

       set nocount on; 

       declare      @varXML varchar(max),
                            @xml as xml,
                            @IDUsuario int,
                            @Motivo varchar(50)

       select @varXML = cast(context_info() as varchar(max))
       select @xml = cast(substring(@varXML, 1, charindex(‘>’, @varXML)) as xml)
      

       select        @IDUsuario = x.v.value(‘@IDUsuario[1]’,‘int’),
                           @Motivo = x.v.value(‘@Motivo[1]’,‘varchar(50)’)
       from          @xml.nodes(‘/Values’) x(v)
      

       insert into DeletedTeste(IDTeste, Descricao, Motivo, ExcluidoPor, DataExclusao)
       select        IDTeste,
                           Descricao,
                           @Motivo,
                           @IDUsuario,
                           getdate()
       from          deleted

end

A trigger é disparada toda vez que algum registro da tabela Teste é removido. Nesse caso, a trigger pega os dados do CONTEXT_INFO e transforma-o em um VARCHAR.

select @varXML = cast(context_info() as varchar(max))

Depois disso, a variável é “limpa”, pois caso você veja o tamanho real da variável, vai perceber que existem diversos caracteres além do XML e esses caracteres, mesmo que ocultos, impedem que seja feita uma conversão de VARCHAR para XML.

Nesse caso, usei essa instrução para fazer essa limpeza e já converter para uma variável do tipo XML.

select @xml = cast(substring(@varXML, 1, charindex(‘>’, @varXML)) as xml)

Feito isso, já temos um XML com os dados passados pela procedure. Agora utilizamos XQuery para pegar os dados do XML, no nosso caso, o ID do usuário e o motivo.

select @IDUsuario = x.v.value(‘@IDUsuario[1]’,‘int’),
             @Motivo = x.v.value(‘@Motivo[1]’,‘varchar(50)’)
from   @xml.nodes(‘/Values’) x(v)

Feito isso, nas variáveis @IDUsuario e @Motivo, já temos os dois dados que precisamos para colocar na tabela de auditoria e o resto dos dados vem da tabela deleted da própria trigger. Assim, inserimos os dados como a seguir:

insert into DeletedTeste (IDTeste, Descricao, Motivo, ExcluidoPor, DataExclusao)
select  IDTeste,
              Descricao,
              @Motivo,
              @IDUsuario, 
              getdate()
from    deleted

Esse é apenas um exemplo do uso do CONTEXT_INFO. Agora é só utilizar para o propósito que for necessário.

Em breve, outras dicas de programação.

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: