Do que se trata o artigo:
Este artigo refere-se à utilização de triggers no SQL Server. Ele apresenta os três tipos de triggers existentes: DML, DDL e de logon. Além disso, demonstra como utilizá-los e como trabalhar com diversas configurações acerca deste mecanismo.
Em que situação o tema útil:
As informações contidas neste artigo, com os variados exemplos apresentados, são úteis para se compreender os diferentes tipos de triggers existentes no SQL Server e a maneira de utilizá-los. Assuntos como recursividade e criptografia também podem ser elucidados através deste artigo.
Resumo DevMan:
O SQL Server possui um mecanismo que dispara automaticamente instruções programadas, quando determinado evento ocorre no banco de dados. Este mecanismo chama-se trigger, e pode ser utilizado em três tipos distintos de eventos: eventos DDL, eventos DML e eventos de Logon. É sobre este assunto que abordaremos neste artigo.
O SQL Server possui um mecanismo que dispara automaticamente instruções programadas, quando determinado evento ocorre no banco de dados. Este mecanismo chama-se trigger, e pode ser utilizado em três tipos distintos de eventos: eventos DDL (Data Definition Language), eventos DML (Data Manipulation Language) e eventos de Logon.
Consequentemente, uma trigger DDL será disparada quando um evento DDL ocorrer. Por sua vez, uma trigger DML entrará em ação quando uma operação de manipulação de dados acontecer. E por fim, quando um evento de logon ocorrer, uma trigger de logon criada será disparada.
Dessa forma, este artigo apresentará os tipos de triggers existentes, com exemplos de utilização e configurações para um melhor aproveitamento do recurso.
Eventos que disparam triggers
Eventos de manipulação de dados ocorrem quando são executadas instruções do tipo Insert, Update ou Delete, alterando através de tabelas ou views os dados de uma base.
Com uma trigger do tipo DML o usuário é capaz, por exemplo, de verificar informações dentro de uma tabela antes de um dado ser inserido na mesma. Pode, do mesmo jeito, analisar ou até atualizar informações em outras tabelas. Com isso, o desenvolvedor pode, por exemplo, verificar se existe realmente a quantidade em estoque do item que está sendo vendido. Ou seja, antes de cadastrar o registro na tabela de vendas, uma informação poderia ser verificada na tabela de estoque, através de uma trigger DML.
Outra vantagem de uma trigger DML é a segurança que ela pode oferecer em relação a não permitir que dados indesejáveis sejam persistidos no objeto, ajudando assim a garantir a consistência das informações. Apesar de o SQL Server disponibilizar a opção de se criar constraints do tipo Check, a trigger permite o desenvolvimento de instruções mais complexas. Além disso, existe a possibilidade de se criar várias triggers de Insert, Update ou Delete para uma mesma tabela, de forma a poder estipular várias ações diante de uma mesma instrução de manipulação de dados.
Eventos de definição de dados acontecem sempre que um objeto é criado, destruído ou quando sua estrutura é alterada. Ou seja, triggers DDL são disparadas principalmente através dos comandos Create, Drop e Alter. E elas são acionadas somente depois da execução das instruções DDL que as dispararam. Seu escopo abrange tanto uma base de dados quanto o próprio servidor, isto é, a instância em que o SQL Server está sendo executado. Dessa forma, a principal utilidade deste tipo de trigger é a de se criar auditorias, para que seja possível monitorar alterações que comprometam a segurança e a integridade do banco ou do servidor.
Já os eventos de logon acontecem no momento em que uma sessão do SQL Server é estabelecida. Então, caso exista uma trigger de logon, esta será disparada logo após o término da autenticação, e antes da sessão ser completamente estabelecida. Consequentemente, se a autenticação falhar, a trigger não será disparada. Além disso, mensagens de erro adicionadas dentro da trigger, em vez de serem apresentadas ao usuário, serão colocadas dentro do log de erros do SQL Server.
Esse tipo de trigger pode ser útil para rastrear conexões realizadas, verificando os usuários que estão fazendo o logon, numa espécie de auditoria. Eventualmente, pode-se bloquear determinados logons ou até mesmo limitar a quantidade de sessões para um logon.
Criando triggers de logon
O script demonstrado na Listagem 1 criará uma trigger de logon que limitará o número de conexões simultâneas permitidas para o login Roberson. Neste exemplo, será limitado a três conexões. No entanto, antes disso, será criado o login Roberson e a ele será dada a permissão View Server State, que faz com que o login tenha permissão sobre alguns objetos e recursos do servidor, como por exemplo, a utilização da procedure sp_lock, a visualização de informações através do activity monitor e, é claro, a utilização da trigger criada sobre o servidor.
Listagem 1. Criando trigger de logon.
Use master;
Go
Create Login Roberson With Password = '123456';
Go
Grant View Server State to Roberson;
Go
Create Trigger utg_LimitaQuantidadeConexoes on All Server
With Execute as 'Roberson'
For Logon as
Begin
If (Original_Login() = 'Roberson') and
((Select
Count(*)
From
sys.dm_exec_sessions
Where
(is_user_process = 1) and
(text_size >= 0) and
(original_login_name = 'Roberson')) = 3)
Begin
Raiserror('O número de conexões abertas permitido (3) para o login "Roberson" foi atingido atingido.', 10, 1);
Rollback;
End
EndDeste modo, ao tentar abrir quatro sessões para o login Roberson, uma mensagem de erro do próprio SQL Server aparecerá, informando a impossibilidade de se ter uma quarta conexão para este login. Se o log de erros do SQL for consultado, será vista a mensagem contida no comando Raiserror.
Criando triggers DDL
As triggers DDL podem ser acionadas a partir de eventos que ocorram somente no banco especificado ou a partir de eventos do servidor como um todo.
A trigger descrita na Listagem 2, por exemplo, bloqueará a criação de tabelas no banco de dados mencionado na palavra-chave Use. Ocorrerá desta maneira porque na criação da trigger será definido, através do comando on Database, que o escopo será somente do banco de dados. Além disso, a instrução contida na trigger garantirá que o bloqueio só irá acontecer para usuários que não estiverem na role db_owner.
Listagem 2. Criando trigger DDL no escopo do banco.
Use Nome_Banco;
go
Create Trigger ProibeCriacaoTabela_Banco on Database
For Create_Table as
Begin
if (is_member('db_owner') = 0)
begin
Print 'Você não tem permissão para criar tabelas!'
Rollback
end
End ... 
