Exemplo de accionamento do SQL Server

Por: Daniel Farina | Actualizado: 2019-02-26 | Comentários (7) | Related: Mais > Triggers

Problem

Você já aprendeu como escrever consultas doSQL e procedimentos armazenados, mas agora você quer aprender sobre triggers de Servidor SQL. Esta dica servirá como ponto de partida e um guia para criar triggers SQL Server.

Solução

Triggers são um dos tópicos mais mal compreendidos para pessoas novas no SQL Server.Talvez isto se deva ao facto de permitirem quase todas as mesmas funcionalidades como procedimentos armazenados, tornando o programador inexperiente confuso sobre se deve criar um procedimento armazenado ou trigger.

O que é um trigger do SQL Server?

Um trigger do SQL Server é um pedaço de código de procedimento, como um procedimento armazenado que só é executado quando um determinado evento acontece. Existem diferentes tipos de eventos que podem disparar um gatilho. Só para citar alguns, a inserção de linhas numa tabela, uma alteração na estrutura de uma tabela e até mesmo o início de sessão de um utilizador numa instância do SQL Server.

Existem três características principais que tornam os disparadores diferentes dos procedimentos armazenados:

• Os disparadores não podem ser executados manualmente pelo utilizador.
• Não há hipótese de os disparadores receberem parâmetros.
• Não é possível comprometer ou reverter uma transacção dentro de um disparador.

O facto de ser impossível utilizar parâmetros nos disparadores não é uma limitação para receber informação do evento de disparo. Como verá mais adiante, existem alternativas para obter informações sobre o evento de disparo.

Classes of SQL Server Triggers

Existem duas classes de triggers no SQL Server:

• DDL (Data Definition Language) triggers. Esta classe de triggers dispara sobre eventos que alteram a estrutura (como criar, modificar ou largar uma tabela),ou em certos eventos relacionados com o servidor como alterações de segurança ou eventos de actualização de estatísticas.
• DML (Data Modification Language) triggers. Esta é a classe de triggers mais utilizada. Neste caso, o evento de disparo é uma declaração de modificação de dados; pode ser uma declaração de inserção, actualização ou eliminação quer numa tabela quer numa view.

Adicionalmente, os disparadores DML têm tipos diferentes:

• FOR ou AFTER : Estes tipos de disparadores são executados após o fim da declaração de disparo (inserção, actualização ou eliminação).
• INSTEAD OF : Ao contrário do tipo FOR (DEPOIS), os gatilhos INSTEAD OF triggers são executados em vez da instrução de disparo. Por outras palavras, este tipo de gatilho substitui a declaração de disparo. Isto é muito útil em casos em que é necessário ter integridade referencial cruzada de bases de dados.

Qual é a importância dos gatilhos do SQL Server?

Uma das características fundamentais das bases de dados relacionais é a consistência dos dados, o que significa que a informação armazenada na base de dados deve ser sempre consistente para cada sessão e cada transacção. A forma como os motores de bases de dados relacionais como o SQL Server implementam isto é através da imposição de restrições como chaves primárias e chaves externas. Mas por vezes isso não é suficiente.

No SQL Server não há hipótese de impor a integridade referencial entre duas tabelas usando chaves estrangeiras se essas tabelas estiverem em bases de dados diferentes ou em servidores diferentes. Neste caso, a única forma de a implementar é usando triggers.

Como sei quais as linhas que foram actualizadas, inseridas ou eliminadas usando um disparador SQL ServerDML?

No caso de disparadores DML, existem duas tabelas virtuais durante a execução do disparador que detém os dados que estão a ser afectados pela execução do disparador. Essas tabelas são nomeadas inseridas e apagadas e têm a mesma estrutura de tabela que a sua tabela base. Algo a ter em mente é que as tabelas inseridas e apagadas nem sempre estão disponíveis em conjunto (ou seja, pode-se ter a tabela inserida, mas não a tabela apagada ou vice-versa). Encontrará mais informações sobre estas tabelas na seguinte dicaServidor SQL compreendendo tabelas inseridas e eliminadas para triggers DML.

SQL Server DML Trigger Syntax

Na próxima secção de código, verá a sintaxe básica CREATE TRIGGER.

CREATE TRIGGER trigger_name ON { Table name or view name } { FOR | AFTER | INSTEAD OF } { , , }

Adicionalmente, a tabela seguinte descreve cada um dos argumentos da sintaxe de CREATE TRIGGER.

>th>Descrição

Cenários de utilização do gatilho do servidor doSQL

Existem dois cenários claros quando os gatilhos são a melhor escolha: auditoria e aplicação de regras de negócio. Utilizando um gatilho, é possível acompanhar as alterações numa dada tabela escrevendo um registo de registo com informação sobre quem fez a alteração e o que foi alterado na tabela.

Talvez pense que pode fazer o mesmo na aplicação com um procedimento armazenado que trata da modificação de dados como inserções e actualizações. Pode utilizar um procedimento armazenado, mas nesse caso não poderá registar as alterações que foram feitas directamente na base de dados a partir do exterior da aplicação.

O mesmo acontece quando se pretende fazer cumprir as regras de negócio com um procedimento armazenado.Se alguém modificar os dados na tabela de base a partir do exterior da aplicação pode ter um problema porque a consistência dos dados não pode ser garantida. Para evitar este problema, certificar-se-ia de que o procedimento armazenado era a única forma de aceder à tabela.

Sample SQL Server DML Trigger

Vamos supor que temos uma base de dados para o departamento de recursos humanos. Com a ajuda de um gatilho podemos manter um registo de auditoria numa tabela separada que contém cada modificação de registo, assim como o utilizador que fez a modificação e a hora em que ela aconteceu.

P>Primeiro, temos de criar a tabela Empregados.

CREATE TABLE Employees ( EmployeeID integer NOT NULL IDENTITY(1, 1) , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) PRIMARY KEY CLUSTERED (EmployeeID) )GO

Então, temos de criar a tabela EmpregadosAuditoria para armazenar os registos de auditoria. Esta tabela tem a mesma estrutura que a tabela dos Empregados, mas inclui uma coluna AuditId como chave primária, ModifiedDate para manter a data da modificação, ModifiedBy para que se possa saber quem modificou a tabela dos Empregados e finalmente Operation onde indicaremos a operação DML que gerou o registo de auditoria com uma das três letras (I forinsert, U for Update e D for Delete).

CREATE TABLE EmployeesAudit ( AuditID INTEGER NOT NULL IDENTITY(1, 1) , EmployeeID INTEGER , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) , ModifiedBy VARCHAR(128) , ModifiedDate DATETIME , Operation CHAR(1) PRIMARY KEY CLUSTERED ( AuditID ) )GO

Para podermos testar o gatilho da amostra, precisamos de adicionar alguns dados à tabela de Empregados.

INSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary )SELECT 'Mark Smith', 'Ocean Dr 1234', 10000UNION ALLSELECT 'Joe Wright', 'Evergreen 1234', 10000UNION ALLSELECT 'John Doe', 'International Dr 1234', 10000UNION ALLSELECT 'Peter Rodriguez', '74 Street 1234', 10000GO

Agora que temos o ambiente de teste definido, é altura de criar o nosso gatilho. Veja o código abaixo.

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees FOR INSERT, UPDATE, DELETEAS DECLARE @login_name VARCHAR(128) SELECT @login_name = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID IF EXISTS ( SELECT 0 FROM Deleted ) BEGIN IF EXISTS ( SELECT 0 FROM Inserted ) BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'U' FROM Deleted D END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'D' FROM Deleted D END END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT I.EmployeeID , I.EmployeeName , I.EmployeeAddress , I.MonthSalary , @login_name , GETDATE() , 'I' FROM Inserted I ENDGO

Basicamente o código consiste em obter o utilizador que está a modificar o Employeestable, olhando para o sys.dm_exec_sessions Dynamic ManagementView para a sessão com o SPID actual. Depois disso, o disparador insere um registo na tabela EmployeesAudit para cada registo inserido, actualizado ou apagado na tabelaEmployees, bem como a hora actual e a operação DML que disparou o disparador.

A fim de testar o disparador, criei três consultas. Coloquei o código dentro da atransacção apenas para manter a ordem no meu ambiente de teste, pode omitir isto.

A primeira dessas consultas é uma actualização.

BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 UPDATE EmployeesSET EmployeeName = 'zzz'WHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION

Na próxima captura de ecrã verá o registo actualizado na tabela Empregados e o novo registo na EmployeesAudit que mantém o registo da operação DML sobre a tabela Empregados.

A segunda consulta é uma inserção de duas filas na tabela Empregados.

BEGIN TRANSACTIONINSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary ) SELECT 'zz' , 'dsda' , 10000 UNION ALL SELECT 'Markus Rubius' , 'dsda' , 6000SELECT *FROM dbo.EmployeesSELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION

Na captura de ecrã seguinte verá as duas filas inseridas na tabela EmpregadosAuditoria e o seu respectivo registo de auditoria na tabela EmpregadosAuditoria.

Finalmente, a terceira consulta é uma declaração de eliminação na tabela Empregados.

BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1DELETE FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1ROLLBACK TRANSACTION

Na próxima captura de ecrã verá a linha eliminada da tabela de Empregados e o seu respectivo registo de auditoria na tabela de EmpregadosAuditores.

Passos seguintes

• Se tiver dificuldade em compreender a forma como captei a operação de DML que foi realizada na tabela EmployeesAuditoria dê uma olhadela nesta dica:Compreender o SQL Server inserido e as tabelas apagadas para gatilhos de DML.
• Para um exemplo de um INSTEAD OF trigger pode verificar esta dica:Usando INSTEAD OF triggers no SQL Server para operações de DML.
• Pode encontrar mais informações sobre o sys.dm_exec_sessions DMV aqui:Compreender e Usar sys.dm_exec_sessions no SQL Server.

li>Para uma explicação mais detalhada do comando EXECUTE AS dê uma vista de olhos na próxima dica:Concedendo permissão com o comando EXECUTE AS no SQL Server.li>Fica ligado à Categoria de Dicas para mais dicas e truques.

br>Último Actualizado: 2019-02-26

botão da próxima dica

h5>sobre o autor

Daniel Farina nasceu em Buenos Aires, Argentina. Autodidacta, desde a infância mostrou uma paixão pela aprendizagem.
Ver todas as minhas dicas

Recursos Relacionados
ul>>li>Mais Dicas de Desenvolvimento de Bases de Dados…