Trabalhando com SQL CLR: Trigger

Neste último artigo do ano, trataremos o desenvolvimento de trigger em CLR, que basicamente são gatilhos disparados quando há alguma inserção, atualização ou exclusão de dados de uma tabela.

Assim como qualquer implementação CLR, triggers em CLR também têm que ser justificáveis, pois assim como as tradicionais triggers em T-SQL, elas também fazem uso das estruturas INSERTED e DELETED para identificar o que foi realizado, isso quer dizer que são necessárias consultas ao banco de dados para recuperar estas informações, e consultas ao banco de dados é um processo que as triggers tradicionais fazem melhor que as desenvolvidas em CLR, já que não dependem de uma estrutura de conexão.

Recomendações feitas, vamos ao desenvolvimento da nossa trigger, o primeiro passo é criar uma tabela para os testes, no caso a tabela “Modelo”.

CREATE TABLE Modelo (
	Codigo INT IDENTITY PRIMARY KEY,
	Nome VARCHAR(40)
)

Criada a tabela, utilizamos o template de trigger do Visual Studio, definindo como Target a nossa tabela de teste, para responder os eventos de INSERT, UPDATE e DELETE.

Através da classe SqlContext é possível recuperar informações do contexto da trigger (TriggerContext) e descobrir qual é o tipo de ação/evento que a trigger esta respondendo (TriggerAction), e nesta implementação simples retornamos esta informação pelo já conhecido método Pipe.Send:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
    [SqlTrigger(Name = "TRG_Quinto"
        , Target = "Modelo"
        , Event = "FOR INSERT, UPDATE, DELETE")]
    public static void Quinto()
    {
        var action = SqlContext.TriggerContext
            .TriggerAction
            .ToString()
            .ToUpper();

        SqlContext.Pipe.Send(action);
    }
}

Após feito o deploy da trigger, basta testar:

INSERT INTO Modelo
SELECT Nome FROM dbo.Cliente

UPDATE Modelo
SET Nome = UPPER(Nome)

DELETE FROM Modelo

Para o segundo exemplo, desenvolveremos uma trigger que gravará em arquivos as alterações realizadas.

Visto que é necessário acesso a um recurso externo ao SQL Server, alteramos o Assembly para ter permissão a este recurso:

E também alteramos o banco de dados para permitir isso:

ALTER DATABASE MeuBancoDeDados SET TRUSTWORTHY ON

Assim nesta implementação, recuperaremos informações das estruturas INSERTED e DELETED em formato XML, em seguida salvamos estas informações em arquivos na pasta “C:\TEMP”:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
    [SqlTrigger(Name = "TRG_Quinto"
        , Target = "Modelo"
        , Event = "FOR INSERT, UPDATE, DELETE")]
    public static void Quinto()
    {
        string result;

        var action = SqlContext.TriggerContext.TriggerAction;
        var guid = Guid.NewGuid();
        var date = DateTime.Today.ToString("yy_MM_dd");

        var pathFolder = string.Format(@"C:\TEMP\{0}", date);
        var path = string.Format(@"{0}\{1}_{2}.txt", pathFolder, action, guid);

        var query =
@"SELECT
  ISNULL(I.Codigo, D.Codigo) AS Codigo
, D.Nome AS Antes
, I.Nome AS Depois
, SUSER_NAME() AS Usuario
FROM INSERTED I
FULL OUTER JOIN DELETED D
ON I.Codigo = D.Codigo
FOR XML AUTO";

        using (var conn = new SqlConnection("Context Connection = True"))
        {
            conn.Open();

            var cmd = new SqlCommand(query, conn);

            result = (string)cmd.ExecuteScalar();
        }

        if (!Directory.Exists(pathFolder))
            Directory.CreateDirectory(pathFolder);

        File.WriteAllText(path, result);
    }
}

Após feito o deploy da trigger, testamos novamente:

INSERT INTO Modelo
SELECT Nome FROM dbo.UDF_Segundo()

UPDATE Modelo
SET Nome = UPPER(Nome)

DELETE FROM Modelo

E o resultado esperado:

Então pessoas, este foi o ultimo arquivo que eu preparei desta série sobre a integração CLR do SQL Server, permitindo uma visão geral desta integração. Agora fica por parte de vocês a criativadade para novas implementações.

Ainda não tive novas ideias para novos artigos sobre CLR SQL, mas se você tiver alguma dúvida ou sugestão, fique a vontade em comentar!

Feliz Ano Novo!!

3 pensamentos sobre “Trabalhando com SQL CLR: Trigger

  1. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

  2. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

Deixe uma resposta

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