SQL Server Basico 5.2 – Triggers

Este é o segundo de uma seria de 3 posts sobre Trigger, o primeiro, que pode ser encontrado neste link, fala sobre triggers basicas de comandos DML, ou seja, insert, update e delete, este aqui, falarei sobre a possibilidade de trigger para comandos DDL, ou seja, criar uma trigger que seja disparada toda vez que um comando CREATE TABLE ou um comando ALTER PROCEDURE for executado.

Como primeira explicação, é necessario saber que triggers DDL são divididas em 2 sub-categorias:
– De base de dados (Ex: Criação de uma tabela)
– De instancia (Ex: Criação de uma base de dados)

Abaixo alguns exemplos das ações cujas quais podem ser alvos de triggers DDL:

Nivel de Base de dados:
– CREATE INDEX
– ALTER VIEW
– CREATE TABLE

Nivel de Instancia:
– DROP ENDPOINT
– CREATE DATABASE
– REVOKE SERVER

Enfim, a lista completa contem inumeras possibilidades e não seria inteligente colocar todas aqui, sendo que a lista completa se encontra aqui disponivel gratuitamente no Books Online.

Porem…Por que utilizar triggers DDL?
Existem muitas respostas para essa pergunta, inclusive uma dela pode ser encontrada aqui neste meu outro artigo , no caso este artigo, contem alguas outras informações que serão discutidas no proximo posts sobre triggers.
Uma das melhores respostas é que no SQL Server 2005 (Menor versão que suporta triggers DDL), é um meio de auditoria muito eficar, talvez cause um pouco de queda de performance, mas é provavelmente uma das melhores auditorias possivel do SQL Server 2005, no SQL Server 2008 ou SQL Server 2008R2 temos a possibilidade de auditoria nativa, que é muito melhor com certeza, porem, ainda no SQL Server 2008[R2] as triggers DDL são necessarias, por exemplo, um dos tunnings fisicos possivel é separar os indices criados em um outro filegroup e outro disco, com triggers DDL é possivel fazer com que tentativas de criação de indices fora do filegroup correto, não sejam criados, ou então, uma situação que passei semana passada em um cliente:
– Existe o usuario da aplicação, vamos chama-lo de app_user
– app_user, precisa ter permissão de execução em todas as procedure e function
– app_user necessita tambem permissão de select em todas as views
Lembrando que as functions, view e procedures, são criadas o tempo todo, e como os usuarios que criam esses objetos são os desenvolvedores, e não possuem permissão de conceder as execuções ou selects para o usuario, criei uma trigger para os eventos de CREATE_FUNCTION, CREATE_PROCEDURE e CREATE_VIEW, que ao final da trigger, da permissão ao usuario app_user para esse novo objeto criado.
Dessa forma, o permissionamento fica automatico, sem a necessidade de conceder security_admin aos desenvolvedores.

Até o momento esta tudo lindo, porem, uma duvida pode surgir para voces leitores, quando se esta criando uma procedure por exemplo, o nome da mesma é variavel, assim como o usuario que a criou, enfim, existem muitas variaveis nas execuções deste comando, portanto, fica a pergunta: Como é possivel visualisar de forma dinamica todas essas informações? A resposta é muito simples!, Existe uma função ja criada chamada EVENTDATA().

Essa função EVENTDATA() ira retornar um XML com todas as informações possivel de se capturar para cada evento.
Abaixo segue um exemplo da mesma.

CREATE_PROCEDURE
2011-11-28T12:26:50.787
54
LFERREIRA-PC\FABRIZZIO08ENT
sa
dbo
Teste2
dbo
Hora5
PROCEDURE

CREATE PROCEDURE dbo.Hora5
AS
BEGIN
SELECT GETDATE()
END

O que temos então de informação neste XML?

EVENT_TYPE: Tipo de evento, no caso, CREATE_PROCEDURE
POSTTIME: Hora do evento
SPID: Spid que gerou o evento
SERVERNAME: Nome do servidor cujo evento disparou a trigger
LOGINNAME: Login que realizou o evento
USERNAME: Usuario (Como fiz como SA, ele trouxe dbo)
DATABASENAME: Nome da base
SCHEMANAME: Schema que gerou o evento
OBJECTNAME: No caso, objeto criado
OBJECTTYPE: Tipo de objeto
TSQLCOMMAND: Comando TSQL que disparou o evento

Lembrando que esses são os campos para procedures, não necessariamente serão os mesmos para functions e views.

Como meu proximo post sobre triggers sera 100% focado no eventdata() e na leitura xml, aqui colocarei algo bem simples, apenas para que possamos ver uma trigger DDL ser disparada e ja dar liga para o proximo post…. Exemplo:

CRIAÇÃO DA TABELA

CREATE TABLE Teste_XML 
(
EVENTDATA_XML XML
)

CRIAÇÃO DA TRIGGER DLL

CREATE TRIGGER audit_Create_Proc
ON DATABASE
FOR CREATE_PROCEDURE
AS
DECLARE @Event XML
SET @Event = EVENTDATA()
INSERT INTO Teste_XML VALUES (@Event)

CRIAÇÃO DE UMA PROCEDURE DE TESTE

CREATE PROCEDURE dbo.Hora5
AS
BEGIN
SELECT GETDATE()
END

SELECT DE TESTE

SELECT * FROM Teste_XML

Anúncios
Post a comment or leave a trackback: Trackback URL.

Comentários

  • Flávio  On 28/11/2011 at 21:26

    Fala Fabrizzio, tudo bem ?!

    Somente uma crítica construtiva: Tente colocar as partes de códigos em bloco do WordPress, pois essa fonte prejudica quem utiliza para copiar os scripts para estudos e testes!

    Abraço!

    Flavio

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: