BDA - Triggers

Triggers

• Triggers ou gatilhos são stored procedures em PL/SQL ou Java executadas (disparadas) implicitamente
• Facilitam o desenvolvimento de aplicações e a maioria dos BDs relacionais possui a técnica de Trigger que é utilizada para poder colocar uma determinada regra de negócio da aplicação
• Não possuem nenhum “chamamento” explícito, sempre que uma tabela ou view é modificada ou ainda quando acontece alguma determinada ação do usuário ou até mesmo ações do próprio banco de dados.

Uso

• Os Triggers são usados para realizar tarefas relacionadas com:
  • validações
  • restrições de acesso
  • rotinas de segurança
  • consistência de dados
• desta forma estes controles deixam de ser executados pela aplicação e passam a ser executados pelos Triggers em determinadas situações como:
  • mecanismos de validação envolvendo múltiplas tabelas
  • criação de conteúdo de uma coluna derivada de outras colunas da tabela
  • realizar análise e e atualizações em outras tabelas com base em alterações e/ou inclusões da tabela atual

• Triggers são uma parte crítica de qualquer aplicação bem modelada e, através deles, é possível garantir as seguintes funcionalidades:
  • Executar validações de alterações feitas em tabelas ou views:
    • triggers oferecem uma forte garantia na validação de informações, pois esta validação está ligada diretamente ao objeto do banco de dados (tabela ou view)
  • Automatizar manutenções no banco de dados:
    • a partir de algumas versões, é possível associar triggers a eventos do banco de dados, como uma inicialização (startup), permitindo executar tarefas de limpeza na inicialização, por exemplo
  • Aplicar regras a respeito de atividades de administração de uma maneira extremamente granular:
    • pode-se usar triggers para controlar qual o tipo de alteração que se pode fazer em determinado objeto, como apagar (drop) ou alterar uma tabela.

• A criação de um Trigger envolve duas etapas :
  • Um comando SQL que vai disparar o Trigger ( INSERT , DELETE , UPDATE)
  • A ação que o Trigger vai executar ( Geralmente um bloco de códigos SQL )

Eventos

• Eventos que “disparam” um trigger:
  • DML (Data Manipulation Language):
    • sempre que um evento do tipo insert, update ou delete ocorrer na tabela, o trigger será “disparado”
  • DDL (Data Definition Language):
    • o trigger será disparado sempre que um evento DDL ocorrer, como a criação de uma tabela. É muito útil para o caso de auditorias ou para evitar que certas operações sejam executadas
  • Eventos do banco de dados:
    • eventos como startup, shutdown, sempre que um usuário conectar-se ou desconectar-se e até mesmo sempre que um erro ocorrer em alguns SGBDs, o trigger será “disparado”
  • INSTEAD OF:
    • Instead of (em vez de) triggers são uma ótima alternativa aos triggers de DML, pois eles são “disparados” quando um evento do tipo insert, update ou delete estão para acontecer. O código do trigger define o que deverá acontecer no lugar dos eventos. Este tipo de trigger controla operações em views, não em tabelas;

• Um Trigger padrão ocorre depois de algumas verificações do SQL, mas ainda podemos prevenir a ação que executou o Trigger se utilizarmos a instrução ROLLBACK, pois o SQL Server automaticamente inicia uma transação para instruções do tipo INSERT, UPDATE ou DELETE ( transação implícita )

Limitações

• Não é possível criar um Trigger para uma visão porém, quando uma visão é utilizada, os triggers da tabela que está sendo utilizada são normalmente acionados
• O resultado da execução de um Trigger é retornado para a aplicação que o chamou
• O comando WRITETEXT não ativa um Trigger
• O comando TRUNCATE TABLE não pode ser reconhecido por um Trigger
• Não podemos usar em um Trigger os seguintes comandos SQL :
  • ALTER DATABASE , ALTER TRIGGER , ALTER PROCEDURE , ALTER TABLE , ALTER VIEW . CREATE DATABASE , CREATE INDEX , CREATE PROCEDURE, CREATE SCHEMA, CREATE TABLE , DROP DATABASE, DROP TABLE , DROP PROCEDURE, DROP TRIGGER, DROP INDEX, GRANT , LOAD DATABASE, REVOKE, RESTORE DATABASE, TRUNCATE TABLE.

Sintaxe

   ON Table - a tabela para o qual o trigger esta sendo criado
   FOR - deve ser seguido do tipo de comando que acionam o trigger
   AFTER - determina que o trigger somente sera disparado quando todas as rotinas especificadas no comando de disparo 
           forem executadas com sucesso
   INSTEAD OF - Determina que o trigger será executado ao invés do comando de disparo do mesmo.
   [DELETE] [INSERT] [UPDATE] - indicam o tipo de ação que deve disparar o trigger.

Definições

• Para criar um Trigger deve-se definir :
  • O nome
  • A Tabela para o qual o Trigger irá ser criado
  • Quando o Trigger deverá ser disparado
  • Os comandos que determinam qual ação o Trigger deverá executar

Exemplos

Exemplo 1

• O trigger abaixo será disparado quando alguém tentar incluir ou alterar dados na tabela Titles
• Ele emite uma mensagem ao usuário
• 50009 é uma mensagem definida para o usuário em sysmessages.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'reminder' AND type = 'TR')
  DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE 
AS RAISERROR (50009, 16, 10) 
GO

Exemplo 2

• Supondo que tenhamos esta tabela criada em um banco (Ex.: SQL Server):

CREATE
TABLE DBO.TESTE
(
COD INT
)

Um Trigger de INSERT pode ser criado da seguinte maneira:

CREATE
TRIGGER T_INCLUI ON TESTE
FOR
INSERT 
AS 
SELECT
‘DADOS A SEREM INSERIDOS:’
SELECT
* FROM INSERTED
GO

• Dentro de um Trigger de INSERT podemos utilizar a tabela temporária INSERTED (criada somente durante o Trigger) para visualizarmos os dados que estão sendo inseridos.

• Se utilizarmos esta instrução:

INSERT INTO TESTE VALUES (1)

• O Trigger será disparado e mostrará o valor 1 para o campo COD da tabela INSERTED.

• Um exemplo de um Trigger de DELETE:

CREATE
TRIGGER T_APAGA ON TESTE
FOR 
DELETE
AS
SELECT
‘DADOS A SEREM APAGADOS:’
SELECT
* FROM DELETED
GO

• Ao invés de utilizarmos a tabela temporário INSERTED utilizamos a tabela temporária chamada DELETED, com os dados que serão apagados.

• Um Trigger de UPDATE:

CREATE
TRIGGER T_ALTERA ON TESTE
FOR 
UPDATE
AS
SELECT
‘DADOS ANTIGOS:’
SELECT
* FROM DELETED
SELECT
‘DADOS NOVOS:’
SELECT
* FROM INSERTED
GO

• Temos duas tabelas temporárias: a tabela DELETED, com os valores antigos para o campo, e a tabela INSERTED com os novos valores para os campos da tabela.

• Supondo que para qualquer instrução INSERT, UPDATE ou DELETE efetuada sobre a tabela TESTE seja cancelada:

CREATE
TRIGGER T_CANCELA ON TESTE
 FOR
INSERT , UPDATE , DELETE
AS
SELECT ‘CANCELANDO AÇÃO.’
ROLLBACK
GO

Dicas

• Pode-se ter mais de um Trigger do mesmo tipo para uma mesma tabela. A ordem de execução vai do que foi criado primeira até o último que foi criado.
• O encadeamente de Trigger (um Trigger chamado outro, ou a si mesmo) pode ocorrer, desde que seja habilitada uma opção do servidor. Por padrão esta opção é habilitada.
• Triggers nas regras de negócio devem ser usados sem abuso. Para verificações mais simples, pode-se utilizar outros objetos do Banco de dados, como constraints
• Triggers podem ser utilizados para replicação (cópia dos dados). Por exemplo: a cada vez que o usuário inserir um dados, você insere em outra tabela.
• Triggers não podem ser chamados manualmente como Stored Procedures. Eles são chamados somente pela instrução determinada pelo tipo do Trigger.
• Existem os novos Trigger INSTEAD OF que funcionam um pouco diferente dos Triggers padrão. Eles substituem a instrução que os disparou.

Cases

• Migrar dados de um banco para outro

create or replace 
TRIGGER "TG_CT_NV_NUM_VIRTUAL_04"
AFTER INSERT ON planeta.CT_NV_NUM_VIRTUAL
REFERENCING
 NEW AS NEW
FOR EACH ROW

• Autor..........:
• Data de criacao: 29/09/2012
• Objetivo.......: Esta trigger ira replicar os dados da tabela planeta.CT_NV_NUM_VIRTUAL da Planeta V3 para a Planeta V4

DECLARE
arealocalcod    varchar2(30);
localidadecod   varchar2(20);
Begin
   Begin
       select ctcdarealocalcod,ctcdlocalidadecod into arealocalcod,localidadecod
        from planeta.ct_ct_prefixo@lk_planv3_planv4
        where :new.ctnumVT between ctnufaixainicial and ctnufaixafinal ;
   Exception when others then
       arealocalcod       :=  :new.ctcodloc;
       localidadecod      :=  :new.ctcodloc;
   End;
   Insert into Planeta.Ct_Nv_Num_Virtual@lk_planv3_planv4 
        (ctnunuma, ctnunumvt,ctcdarealocalcod,ctcdlocalidadecod, ctdtdata,ctnmusuario)
        values 
        (:new.ctnuma, :new.ctnumVT, arealocalcod, localidadecod , :new.ctdtevento, 'PLANETA');
Exception when others then
 null;
End;

Referências

• Mauro Pichiliani. Utilizando Triggers para regras de Negócio. iMasters. SQL Server. 2001.
• Triggers no Oracle. SQL Magazine. DevMedia.