Hélio Engholm Jr

Especializado em Engenharia de Software

Triggers no SQL Server: teoria e prática

Este artigo apresenta como trabalhar com triggers no SQL Server, entendendo seu funcionamento e como criá-los através de exemplos práticos.

Ele apresenta os seguintes exemplos:

1.   Exemplo I – Criando trilha de auditoria

2.   Exemplo II – Gatilho que atualiza tabela de caixa automaticamente

3.    Exemplo III- Implementando uma regra de negócio

 

Introdução

Alguns SGBDs (Sistemas Gerenciadores de Bancos de Dados) dispõem de diversas funcionalidades que, se utilizadas corretamente, podem trazer diversos benefícios, tais como:

  • Maior facilidade na manutenção do sistema depois de implantando em ambiente de produção;
  • Ganho de desempenho, quando o banco de dados encontra-se em um servidor com boa capacidade de hardware;
  • Possibilidade de maior atuação de um DBA (Administrador de Banco de Dados) no desenvolvimento e manutenção do sistema;

Definição de Triggers

O termo trigger (gatilho em inglês) define uma estrutura do banco de dados que funciona como uma função que é disparada mediante a ocorrência de algum evento geralmente relacionados a alterações nas tabelas por meio de operações de inserção, exclusão e atualização de dados (insert, delete e update).

Um gatilho está intimamente relacionado a uma tabela, sempre que uma dessas ações é efetuada sobre essa tabela, é possível dispará-lo para executar alguma tarefa.

Usando tabelas deleted e inserted

Ao criarmos um trigger, normalmente precisaremos indicar se estamos nos referindo ao valor de uma coluna antes ou depois da ação de gatilho mudá-la. Por este motivo, duas tabelas virtuais com nomes especiais são criadas para nos ajudar: a tabela deleted que contém cópia de linhas que são excluídas da tabela que possui trigger e outra chamada inserted que contém cópias de linhas que são inseridas na tabela que possui trigger. Nos exemplos deste artigo você visualizará o uso destas tabelas.

Triggers no SQL Server

No SQL Server, utilizamos instruções DML (Data Manipulation Language) para criar, alterar ou excluir um trigger.

A sintaxe para criação de um trigger é:

CREATE TRIGGER [schema_name.] [NOME DO TRIGGER]

ON {[NOME DA TABELA] | view_name}

[WITH dml_trigger_option [,…]]

{FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE]}

[WITH APPEND]

{AS  sql_statement   | EXTERNAL NAME method_name}

 Sendo:

  • NOME DO TRIGGER: nome que identificará o gatilho como objeto do banco de dados. Deve seguir as regras básicas de nomenclatura de objetos.
  • NOME DA TABELA: tabela à qual o gatilho estará ligado, para ser disparado mediante ações de insert, update ou delete.
  • FOR/AFTER/INSTEAD OF: uma dessas opções deve ser escolhida para definir o momento em que o trigger será disparado. FOR é o valor padrão e faz com o que o gatilho seja disparado junto da ação. AFTER faz com que o disparo se dê somente após a ação que o gerou ser concluída. INSTEAD OF faz com que o trigger seja executado no lugar da ação que o gerou.
  • INSERT/UPDATE/DELETE: uma ou várias dessas opções (separadas por vírgula) devem ser indicadas para informar ao banco qual é a ação que dispara o gatilho. Por exemplo, se o trigger deve ser disparado após toda inserção, deve-se utilizar AFTER INSERT.
  • sql_statement: SQL executado quando da execução do trigger.

Veja os exemplos abaixo para facilitar o entendimento.

Nota: Database Engines permitem criar vários gatilhos para cada ação (INSERT, UPDATE e DELETE). Por padrão, não há nenhuma ordem definida na qual vários gatilhos para uma determinada ação de modificação são executados. Podemos definir a ordem usando o primeiro e o último gatilho.

Exemplo I – Criando trilha de auditoria

Este exemplo mostra como criar uma trilha de auditoria de atividades em uma ou mais tabelas do banco de dados utilizando gatilhos. Ele cria a tabela audit_budget, que armazena todas as modificações da coluna orçamento da tabela de projeto. Será realizada gravação de todas as modificações desta coluna através do gatilho modify_budget. Qualquer modificação do orçamento usando a instrução UPDATE ativa o gatilho. Ao fazê-lo, os valores das linhas dos quadros apagados e inseridos são atribuídos às variáveis ​​correspondentes @budget_old, @budget_new, o número @project_. Os valores atribuídos, juntamente com o nome de usuário e a data atual, serão posteriormente inseridos na tabela audit_budget.

USE sample;

GO

CREATE TABLE audit_budget

(project_no CHAR(4) NULL,

user_name CHAR(16) NULL,

date DATETIME NULL,

budget_old FLOAT NULL, budget_new FLOAT NULL);

GO

CREATE TRIGGER modify_budget

ON project AFTER UPDATE

AS

IF UPDATE(budget)

BEGIN

DECLARE @budget_old FLOAT

DECLARE @budget_new FLOAT

DECLARE @project_number CHAR(4)

SELECT @budget_old = (SELECT budget FROM deleted)

SELECT @budget_new = (SELECT budget FROM inserted)

SELECT @project_number = (SELECT project_no FROM deleted)

INSERT INTO audit_budget VALUES (@project_number,USER_NAME(),GETDATE(),@budget_old, @budget_new)

END

Exemplo II – Gatilho que atualiza tabela de caixa automaticamente

Neste exemplo, tomaremos como cenário uma certa aplicação financeira que contém um controle de caixa e efetua vendas. Sempre que forem registradas ou excluídas vendas, essas operações devem ser automaticamente refletidas na tabela de caixa, aumentando ou reduzindo o saldo.

Vamos então criar as tabelas que utilizaremos neste exemplo e inserir o primeiro registro no caixa.

 

Criando as tabelas do exemplo

CREATE TABLE CAIXA

(

DATA                     DATETIME,

SALDO_INICIAL        DECIMAL(10,2),

SALDO_FINAL          DECIMAL(10,2)

)

GO

 

INSERT INTO CAIXA

VALUES (CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103)), 100, 100)

GO

 

CREATE TABLE VENDAS

(

DATA  DATETIME,

CODIGO        INT,

VALOR DECIMAL(10,2)

)

GO

 

Por lógica, o saldo final do caixa começa igual ao saldo inicial.

 

Vamos criar agora o primeiro trigger sobre a tabela de vendas, que irá reduzir o saldo final do caixa na data da venda quando uma venda for inserida.

 

Criando o Trigger no insert na tabela de vendas

 

CREATE TRIGGER TGR_VENDAS_AI

ON VENDAS

FOR INSERT

AS

BEGIN

DECLARE

@VALOR       DECIMAL(10,2),

@DATA         DATETIME

 

SELECT @DATA = DATA, @VALOR = VALOR FROM INSERTED

 

UPDATE CAIXA SET SALDO_FINAL = SALDO_FINAL + @VALOR

WHERE DATA = @DATA

END

GO

Neste trigger utilizamos uma tabela temporária chamada INSERTED. Essa tabela existe somente dentro do trigger e possui apenas uma linha, contendo os dados do registro que acabou de ser incluído. Assim, fazemos um select sobre essa tabela e passamos o valores de suas colunas para duas variáveis internas, @VALOR e @DATA, que são utilizadas posteriormente para realizar o update na tabela de caixa.

O que fazemos é atualizar o saldo final da tabela caixa, somando o valor da venda cadastrada, no registro cuja data seja igual à data da venda (lógica de negócio simples).

Como sabemos que o saldo final da tabela caixa encontra-se com o valor 100,00, podemos testar o trigger inserindo um registro na tabela vendas. Vamos então executar a seguinte instrução SQL e observar seu resultado.

Inserindo uma venda

INSERT INTO VENDAS

VALUES (CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103)), 1, 10)

Inserimos uma venda com a data atual, o código 1 e o valor 10,00. Seguindo a lógica definida, o saldo final do caixa agora deverá ser 110,00.

Podemos conferir isso executando um select sobre a tabela CAIXA e observando o resultado.

Agora precisamos criar um trigger para a instrução de delete, que irá devolver o valor ao caixa quando uma venda for excluída.

Trigger no delete na tabela vendas

CREATE TRIGGER TGR_VENDAS_AD

ON VENDAS

FOR DELETE

AS

BEGIN

         DECLARE

         @VALOR       DECIMAL(10,2),

         @DATA         DATETIME

         SELECT @DATA = DATA, @VALOR = VALOR FROM DELETED

         UPDATE CAIXA SET SALDO_FINAL = SALDO_FINAL – @VALOR

         WHERE DATA = @DATA

END

GO

Dessa vez utilizamos a tabela temporária DELETED, que funciona da mesma forma que a INSERTED já citada, porém com os dados do registro que está sendo excluído, em operações de delete e update.

Nota: em operações de upate, o que ocorre na prática é uma exclusão do registro antigo seguida da inserção de um novo registro com os dados atualizados. Então, em triggers para a operação de update podemos utilizar tanto a tabela DELETED quanto a INSERTED.

Podemos agora excluir o registro da tabela VENDAS e verificar como o saldo do caixa é atualizado (deve voltar ao valor 100,00, devido ao cancelamento da venda de 10,00).

Excluindo uma venda

DELETE FROM VENDAS WHERE CODIGO = 1

GO

Listando os registros da tabela CAIXA podemos ver que o saldo final foi atualizado, tendo sido subtraído dele o valor 10,00, conforme esperado.

Conclusão

Com este exemplo bastante simples é possível perceber um ponto muito importante da utilização de triggers para automatização de certas ações. Por exemplo, o programador responsável por esta parte do sistema poderia optar, antes de ler este artigo, por atualizar a tabela de caixa manualmente após cada operação na tabela vendas, utilizando sua linguagem de programação de preferência. Agora, ele apenas precisará se preocupar com o registro e cancelamento da venda, pois a atualização da tabela de caixa será feita automaticamente pelo próprio banco de dados.

Com isso, o sistema em si, ou seja, o aplicativo, tende a ficar mais leve, pois parte da responsabilidade de execução de algumas tarefas foi transferida para o servidor de banco de dados.

Leia mais em: Triggers no SQL Server: teoria e prática aplicada em uma situação real http://www.devmedia.com.br/triggers-no-sql-server-teoria-e-pratica-aplicada-em-uma-situacao-real/28194#ixzz37TylIA8w

Exemplo III- Implementando uma Regra de Negócio

Este exemplo mostra como utilizar um gatilho para implementar uma regra de negócio. Ele cria uma regra que controla a modificação do orçamento para os projetos. O gatilho total_budget testa cada modificação dos orçamentos e executa apenas as instruções UPDATE, onde a modificação não aumenta a soma de todos os orçamentos em mais de 50 por cento. Caso contrário, a instrução UPDATE é revertida usando a instrução ROLLBACK TRANSACTION.

USE sample;

GO

CREATE TRIGGER total_budget

ON project AFTER UPDATE

AS IF UPDATE (budget)

BEGIN

 DECLARE @sum_old1 FLOAT

DECLARE @sum_old2 FLOAT

DECLARE @sum_new FLOAT

SELECT @sum_new = (SELECT SUM(budget) FROM inserted)

SELECT @sum_old1 = (SELECT SUM(p.budget)

FROM project p WHERE p.project_no

NOT IN (SELECT d.project_no FROM deleted d))

SELECT @sum_old2 = (SELECT SUM(budget) FROM deleted)

IF @sum_new > (@sum_old1 + @sum_old2)  *1.5

BEGIN

PRINT ‘No modification of budgets’

ROLLBACK TRANSACTION

END

ELSE

PRINT ‘The modification of budgets executed’

END




+ Artigos