Arquivos de tags: gatilhos

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

SQL Server Basico 5 – Triggers

Triggers, quando traduzidas, querem dizer gatilhos, gatilhos são disparados, no caso de uma trigger, por evento, uma trigger pode disparar por a exemplo:

Contexto de tabela
– A cada insersão
– A cada deleção
– A cada atualização

Contexto de instancia
– Cada logon
– Cada criação ou deleção de uma tabela

Portanto, tendo conhecimentos dessas 2 possiveis opções, neste post, irei falar apenas sobre Triggers de tabelas.

Trigger de tabela:
Ira disparar quando algo acontecer em relação a aqueles dados, seja um insert, update ou delete, algumas pessoas podem pensar que é possivel auditar o drop de uma tabela a partir da criação de uma trigger na mesma, mas não é, por que:
Uma tabela -> (Contem) -> Dados, portanto, audita-se apenas dados.
Triggers neste contexto existem para assegurar a validade de seus dados por todo o ambiente, uma vez que estamos falando de um ambiente normalizado automatizando algumas tarefas para evitar retarefa, por exemplo:

SITUAÇÃO
– Existe uma tabela chamada USUARIOS, que contem todos os usuarios da empresa.
– Uma outra tabela denominada EMPRESA, que contem o cargo de cada pessoa dentro da empresa.
– O que aconteceria quando um funcionario fosse demitido e seu nome removido da tabela usuarios?
Basicamente ele deveria ter de ser removido tambem da tabela empresa, uma vez que ele não pertence mais a organização. Talveza melhor opção seja a criação de uma procedure contendo a deleção deste usuario das duas tabelas, porem, mesmo todo o processo mascarado por traz de uma procedure, dentro da mesma, ainda serão executadas 2 deleções, portanto, alguem teve que programar as 2 deleções. Com a utilização de trigger, seria possivel realizar apenas uma, e deixar a trigger realizar essa deleção.
Quando estamos falando de procedure, temos 2 tabelas especiais criadas em tempo de execução das queries que podemos utilizar para capturar os dados sejam eles inseridos ou deletados, quando estamos falando de uma insersão de dados, podemos utilizar a tabela INSERTED, e ela contem exatamente os mesmos campos da tabela em questão, ja para deleção de dados, temos a DELETED, que tambem contem os mesmos campos, quando falamos de update, temos as 2, uma vez que um update é composto de uma deleção + uma insersão.

Representação do exemplo acima com insersão:

--CRIAÇÃO DAS TABELAS
CREATE TABLE Usuarios
(
	Usuario VARCHAR(100),
	Senha	VARCHAR(100)

)

CREATE TABLE Empresa
(
	Nome	VARCHAR(100),
	Cargo	VARCHAR(100)
)

--CARGA DE DADOS
INSERT INTO Usuarios VALUES
('Fabrizzio','Fabri'),
('Giovanni','gio'),
('Camila','Cam')

INSERT INTO Empresa VALUES
('Fabrizzio','Gerente'),
('Giovanni','Coordenador'),
('Caimla','Diretora')

--CRIAÇÃO DA TRIGGER DE INSERSÃO
CREATE TRIGGER T_Insert ON Usuarios
FOR INSERT
AS
DECLARE @Usuario VARCHAR(100)
SET @Usuario = (SELECT Usuario FROM INSERTED)
INSERT INTO Empresa VALUES (@Usuario, NULL)

--PRIMEIRO SELECT DE TESTE
SELECT * FROM Usuarios
/*
Fabrizzio	Fabri
Giovanni	gio
Camila	Cam
*/

SELECT * FROM Empresa
/*
Fabrizzio	Gerente
Giovanni	Coordenador
Caimla	Diretora
*/

--INSERSÃO DE TESTE
INSERT INTO Usuarios VALUES ('Marco','DBA')

--SEGUNDO SELECT DE TESTE
SELECT * FROM Usuarios
/*
Fabrizzio	Fabri
Giovanni	gio
Camila	Cam
Marco	DBA
*/

SELECT * FROM Empresa
/*
Fabrizzio	Gerente
Giovanni	Coordenador
Caimla	Diretora
Marco	NULL
*/

Representação do exemplo acima com deleção:

--CRIAÇÃO DE TRIGGER DE DELEÇÃO
CREATE TRIGGER T_Delet ON Usuarios
FOR DELETE
AS
DECLARE @Usuario VARCHAR(100)
SET @Usuario = (SELECT Usuario FROM DELETED)
DELETE FROM Empresa WHERE Nome LIKE @Usuario

--PRIMEIRO SELECT DE TESTE
SELECT * FROM Usuarios
/*
Fabrizzio	Fabri
Giovanni	gio
Camila	Cam
Marco	DBA
*/

SELECT * FROM Empresa
/*
Fabrizzio	Gerente
Giovanni	Coordenador
Caimla	Diretora
Marco	NULL
*/

--DELEÇÃO DE UMA LINHA DA TABELA USUARIOS
DELETE FROM Usuarios WHERE Usuario LIKE 'Marco'

--SEGUNDO SELECT DE TESTE
SELECT * FROM Usuarios
/*
Fabrizzio	Fabri
Giovanni	gio
Camila	Cam
*/

SELECT * FROM Empresa
/*
Fabrizzio	Gerente
Giovanni	Coordenador
Caimla	Diretora
*/

Claro, que neste caso, a performance da trigger seria pior do que em uma procedure aonde o comando seria executado na propria tabela e não ao disparar de uma trigger, portanto, trigger nem sempre são recomendadas e podem sim serem evitadas, porem, existem muitos sistemas aonde grande parte de integridade dos dados é feita via trigger, outro, aonde o uso de trigger é mais consciente, pense em um software aonde é proibido mexer em seu banco de dados, hoje em dia isso é bem comum, e tudo deve ser feito atravez de telas de sistemas, vamos supor que essas telas chamem procedures, que são responsaveis pela integridade dos dados, a empresa que criou e vendeu o software, pode sim criar uma trigger e fazer com que a mesma não seja disparada caso a ação que a disparou seja a procedure, desta forma, alem de manter a segurança dos dados pela proibição de se mexer do usuario, caso ele altere alguma coisa, não havera falta de integridade dos dados.