wiki

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.
Disponível em “http://3.143.69.156/wiki/index.php?title=BDA_-_Triggers&oldid=18286”