Oracle - Trigger

Triggers são procedimentos que podem ser gravados em Java, PL/SQL ou C. São executados (ou disparados) implicitamente quando uma tabela é modificada, um objeto é criado ou ocorrem algumas ações de usuário ou de sistema de banco de dados...

por Milton Goya

Triggers são procedimentos que podem ser gravados em Java, PL/SQL ou C. São executados (ou disparados) implicitamente quando uma tabela é modificada, um objeto é criado ou ocorrem algumas ações de usuário ou de sistema de banco de dados.

As triggers são similares as stored procedures diferindo, apenas, na maneira como são chamadas. A trigger é executada implicitamente quando ocorre algum evento de trigger enquanto a stored procedure deve ser executado explicitamente.

Uma trigger é composta por quatro partes:

- Momento
- Evento
- Tipo
- Corpo

O momento define quando uma trigger irá ser acionada. Pode ser:

- BEFORE (tabela)
- AFTER (tabela)
- INSTEAD OF (view)

BEFORE indica que os comandos PL/SQL do corpo da trigger serão executados ANTES dos dados da tabela serem alterados. Normalmente usamos BEFORE nos casos em que precisamos incializar variáveis globais, validar regras de negócios, alterar o valor de flags ou para salvar o valor de uma coluna antes de alterarmos o valor delas. Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	BEFORE...
.
.
.
END;
/

AFTER indica que os comando PL/SQL do corpo da trigger será executado APÓS os dados da tabela serem alterados. Normalmente usamos AFTER para completar os dados de outras tabelas e para completar a atividade de outra trigger de momento BEFORE. Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	AFTER...
.
.
.
END;
/

INSTEAD OF indica que a trigger irá ser executada no lugar da instrução que disparou a trigger. Literalmente, a instrução é substituída pela trigger. Essa técnica permite que façamos, por exemplo, alterações em uma tabela através de uma view. É usado nos casos em que a view não pode alterar uma tabela por não referenciar uma coluna com a constraint not null. Nesse caso a trigger pode atualizar a coluna que a view não tem acesso.

Dois detalhes muito importantes sobre INSTEAD OF:

- Só funcionam com views e
- É sempre de linha. Será considerado assim, mesmo que "FOR EACH ROW" for omitido.

Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	INSTEAD OF INSERT ON vemp
FOR EACH ROW
WHEN ...
.
.
.
END;
/

O evento define qual é a instrução DML que aciona a trigger. Informa qual instrução SQL irá disparar a trigger. Pode ser:

- INSERT
- UPDATE
- DELETE

Quando o evento for um UPDATE podemos informar quais colunas que, ao serem alteradas, irão disparar a trigger. O mesmo NÃO ocorre com INSERT e DELETE porque essas instruções sempre afetam a linha por inteiro. Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	AFTER INSERT ON emp
.
.
.
END;
/

O evento pode conter uma, duas ou todas as três operações DML em uma única linha de comando. Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	BEFORE INSERT OR UPDATE OR DELETE ON emp
.
.
.
END;
/

O tipo define quantas vezes uma trigger será executa. A trigger pode ser executada uma vez para a instrução que a disparou ou ser disparada para cada linha afetada pela instrução que disparou a trigger. Pode ser:

- Instrução (STATEMENT)
- Linha (ROW)

Quando a trigger for do tipo instrução ela será disparada uma vez para cada evento de trigger, mesmo que nenhuma linha tenha sido afetada. São úteis para aquelas trigger que eventualmente não alteram dados ou para situações onde o que queremos é uma resposta da trigger, por exemplo, em uma restrição complexa de negócio. Por DEFAULT toda trigger é deste tipo. Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	BEFORE INSERT OR UPDATE OR DELETE ON emp
	FOR EACH STATEMENT
.
.
.
END;
/

Quando a trigger for do tipo linha, a trigger será executada toda vez que a tabela for afetada pelo evento da trigger. Se nenhuma linha for afetada a trigger não será executada. São muito úteis quando a ação da trigger depende dos dados afetados pelo evento da trigger. Exemplo:

CREATE OR REPLACE TRIGGER novo_func
	BEFORE INSERT OR UPDATE OR DELETE ON emp
	FOR EACH ROW
.
.
.
END;
/

O corpo define a ação que uma trigger irá executar quando acionada. O corpo de uma trigger é composto por um bloco PL/SQL, a chamada de uma PROCEDURE ou por um procedimento JAVA. Por definição, o tamanho de uma trigger não pode ultrapassar 32K.

Como, normalmente, precisamos trabalhar com os valores antes e depois da alteração dos dados, a trigger permite que façamos referencia aos valores antes da alteração (OLD) e após a alteração (NEW).

O nome de uma trigger deve ser único dentro de um mesmo esquema, e sua sintaxe básica é:

CREATE [OR REPLACE] TRIGGER [schema.] nome_da_trigger
       [BEFORE|AFTER]
       [DELETE|OR INSERT|OR UPDATE[OF coluna]]
       ON [schema.] nome_da_tabela_ou_da_view
       [REFERENCING [OLD [AS] OLD] [NEW [AS] NEW]
       [FOR EACH ROW]
       [WHEN [condição]]
BLOCO PL/SQL

Onde:

Nome_da_trigger é o nome da trigger;

Nome_da_tabela_ou_da_view indica a tabela ou view associada com a trigger;

Corpo_da_trigger é a ação que a trigger irá executar. Inicia por DECLARE ou BEGIN e termina por END. Também pode conter a chamada de um procedimento.

O uso do nome da coluna na cláusula UPDATE pode aumentar a performance porque a trigger só será disparada quando aquela coluna especificada na cláusula for alterada.

Agora que sabemos como criar uma trigger veremos um exemplo completo:

Primeiro vamos criar uma tabela para gravar um registro de todos os usuários que se conectaram ao banco:

CREATE TABLE vigia
   (marca VARCHAR2(100));
              
CREATE OR REPLACE TRIGGER marca_logon
   AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO sys.vigia
    VALUES (USER || " entrou no sistema em " ||
            TO_CHAR(sysdate, "DD-MM-YYYY HH24:MI:SS"));
  COMMIT;
END;
/

Pronto, temos nossa primeira trigger. Ela registra o nome do usuário e a que horas ele entrou. Esse exemplo foi retirado diretamente da documentação Oracle. No nosso exemplo fazemos referencia a um evento do sistema ao invés de referenciarmos uma tabela. Outros eventos do sistema são:

- AFTER SERVERERROR
- AFTER LOGON
- BEFORE LOGOFF
- AFTER STARTUP
- BEFORE SHUTDOWN

Você pode criar triggers usando os eventos acima para DATABASE e SCHEMA. As duas exceções são SHUTDOWN e STARTUP que só se aplicam a DATABASE. Exemplo:

CREATE OR REPLACE TRIGGER marca_logoff
   BEFORE LOGOFF ON SCHEMA
BEGIN
  INSERT INTO sys.vigia
    VALUES (USER || " saiu do sistema em " ||
            TO_CHAR(sysdate, "DD-MM-YYYY HH24:MI:SS"));
  COMMIT;
END;
/

Eventualmente podemos ter algum tipo de erro em nossa trigger. Para verificar quais são os erros de compilação que temos na trigger basta usar o comando SHOW ERRORS TRIGGER nome_da_trigger. Caso você queira ver os erros de compilação da última trigger que você compilou pode escrever apenas SHOW ERRORS ou SHO ERR. Ao executarmos esse comando ele mostrará a linha onde está o erro. Atenção: caso a linha onde está o erro se estenda por mais de uma linha, este comando indicará o início da linha. Vamos criar uma trigger com erro para servir como exemplo:

CREATE OR REPLACE TRIGGER marca_logon
   AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO sys.vigia
    VALUES (USER || " entrou no sistema em " ||
            TO_CHAR(sysdate, "DD-MM-YYYY HH24:MI:SS));
  COMMIT;
END;
/

Gatilho criado com erro de compilação

Qual é o erro desse gatilho? É um erro bem banal, no caso deixamos de fechar a apóstrofe (ou aspas simples ou quote) no final da instrução TO_CHAR. Ao executarmos o SHOW ERROR ele irá mostrar que houve um erro na linha 4. Isso porque ele aponta onde a linha que contem o erro começou a ser escrita e não a linha onde efetivamente ocorreu o erro está.

Caso precise de mais informações sobre sua trigger, a view USER_TRIGGERS pode fornecer informações muito úteis. Exemplo:

SELECT trigger_name
  FROM user_triggers;

Com o nome da trigger que você deseja analisar execute o comando:

SELECT trigger_type, table_name, triggering_event
  FROM user_triggers
 WHERE trigger_name = "nome_da_trigger";

Ou, se precisar obter o código usado para gerar a trigger:

SELECT trigger_name, trigger_type, triggering_event,
  table_name, referencing_names,
  status, trigger_body
  FROM user_triggers
 WHERE trigger_name = "nome_da_trigger";

Caso descubra que não precisa mais da trigger existe duas formas de tratar a situação. Eliminar a trigger ou desabilitá-la.Eliminando a trigger:

DROP TRIGGER nome_da_trigger;

Caso prefira apenas desabilitar a trigger use o comando:

ALTER TRIGGER nome_da_trigger DISABLE; 

Quando a trigger é criada pela primeira vez ela é habilitada automaticamente. Para habilitar a trigger novamente basta usar o comando:

ALTER TRIGGER nome_da_trigger ENABLE; 

Mas vamos continuar criando nossas triggers. O próximo caso vai nos ajudar a impedir que alguém cadastre um funcionário fora do horário de expediente:

CREATE TABLE nova_emp 
    AS SELECT * FROM SCOTT.EMP;

CREATE OR REPLACE TRIGGER hora_exp
 BEFORE INSERT ON nova_emp
 BEGIN
    IF (TO_CHAR(sysdate,"DY") IN ("SAB","DOM")) OR
       (TO_CHAR(sysdate,"HH24:MI")
               NOT BETWEEN "08:30" AND "17:30")
     THEN RAISE_APPLICATION_ERROR (-20500,"Você só pode
               atualizar os empregados no horário de 
               expediente");
    END IF;
END;
/

Essa trigger pode ser refinada para testar os predicados condicionais. Por exemplo:

CREATE OR REPLACE TRIGGER hora_exp
 BEFORE INSERT OR UPDATE OR DELETE ON nova_emp
 BEGIN
    IF (TO_CHAR(sysdate,"DY") IN ("SAB","DOM")) OR
       (TO_CHAR(sysdate,"HH24:MI")
               NOT BETWEEN "08:30" AND "17:30")
     THEN
        IF DELETING THEN
           RAISE_APPLICATION_ERROR (-20500,"Você só pode
               excluir empregados no horário de expediente");
        ELSIF INSERTING THEN
           RAISE_APPLICATION_ERROR (-20502,"Você só pode
               incluir empregados no horário de expediente");
        ELSIF UPDATING ("SAL") THEN
           RAISE_APPLICATION_ERROR (-20504,"Você só pode
               alterar salarios no horário de expediente");
        ELSE
           RAISE_APPLICATION_ERROR (-20506,"Você só pode
               Fazer alterações no horário de expediente");
        END IF;
    END IF;
END;
/ 

Vamos ver como usar valores OLD e NEW:

Primeiro vamos criar uma tabela para conter os dados do nosso histórico.

CREATE TABLE DDUR
(USUARIO 	VARCHAR2(15),
 HORARIO 	DATE,
 EMPNO	NUMBER(4),
 ENAME    VARCHAR2(10),
 JOB      VARCHAR2(9),
 MGR      NUMBER(4),
 HIREDATE DATE,
 SAL      NUMBER(7,2),
 COMM     NUMBER(7,2),
 DEPTNO   NUMBER(2))
/

Agora vamos criar nossa trigger. Ela deve registrar tudo o que fizermos em nossa tabela.

CREATE OR REPLACE TRIGGER hist_emp
 AFTER INSERT OR UPDATE OR DELETE ON nova_emp
 FOR EACH ROW
 BEGIN
   INSERT INTO ddur VALUES(
          user, sysdate, :OLD.empno, :OLD.ename, :OLD.job,
          :OLD.mgr, :OLD.hiredate, :OLD.sal, :OLD.comm, :OLD.deptno);
END;
/

A referência :OLD indica que estamos usando os valores antes da alteração. Caso quiséssemos usar o valor atualizado a referencia seria :NEW. Ambas podem ser usadas na mesma trigger. Por exemplo, nossa trigger poderia ter sido escrita assim:

CREATE OR REPLACE TRIGGER hist_emp
 AFTER INSERT OR UPDATE OR DELETE ON nova_emp
 FOR EACH ROW
 BEGIN
   INSERT INTO ddur VALUES(
          user, sysdate, :NEW.empno, :NEW.ename, :OLD.JOB,
          :NEW.MGR, :OLD.HIREDATE, :OLD.sal, :OLD.comm, :OLD.deptno);
END;
/

Você pode usar :OLD e :NEW em comparações dentro da sua trigger, montando estruturas PL/SQL cada vez mais complexas. Por exemplo:

CREATE OR REPLACE TRIGGER aumento
       BEFORE UPDATE OF sal ON emp
       FOR EACH ROW
BEGIN
     IF (:NEW.sal - :OLD.sal) < :OLD.sal * 0.025
     THEN
         RAISE_APPLICATION_ERROR (-20512, "Favor corrigir indice");
      END IF;
END;

No caso acima, se o aumento de salário for inferior a 2,5% o sistema avisa que houve um erro na alteração salarial. Em um outro exemplo, mas agora com WHEN

CREATE OR REPLACE TRIGGER ver_sal
       BEFORE INSERT OR UPDATE OF sal, job
       ON empl
       FOR EACH ROW
       WHEN (NEW.job_id <> "PRESIDENT")
DECLARE
       v_minsal emp.sal%TYPE;
       v_maxsal emp.sal%TYPE;
BEGIN
       SELECT MIN(sal), MAX(sal)
         INTO v_minsal, v_maxsal
         FROM emp
        WHERE job = :NEW.job;
       IF :NEW.sal < v_min OR
          :NEW.sal > v_maxsal THEN
          RAISE_APPLICATION_ERROR(-20515,"Salario inválido");
       END IF;
END;
/

UPDATE emp
   SET sal = 3400
 WHERE ename = "BLAKE";

Neste caso estamos garantido que ninguém que for contratado com o cargo diferente de PRESIDENT irá receber um salário menor que o menor salário de seu cargo ou um salário maior que o maior salário de seu cargo.

Uma trigger pode ser bem mais simples do que os exemplos acima. Por exemplo, se quisermos implementar uma restrição onde o salário do funcionário nunca possa ser reduzido, basta aplicarmos a trigger:

CREATE OR REPLACE TRIGGER veri_sal
       BEFORE UPDATE OF sal ON emp
       FOR EACH ROW
       WHEN (NEW.sal < OLD.sal)
BEGIN
     RAISE_APPLICATION_ERROR (-20508,
"O salário não pode ser reduzido");
END;
/

Para que um usuário crie suas próprias triggers ele precisa ter o privilégio de sistema CREATE TRIGGER e ser o proprietário da tabela onde irá criar a trigger. Caso não seja proprietário ele deve ter o privilégio de sistema ALTER ou ALTER ANY TABLE. Caso precise criar triggers para eventos do banco de dados deve ter o privilégio de ADMINISTER DATABASE TRIGGER. Caso a trigger faça chamada de alguma procedure, quem estiver criando a trigger deve ter o privilégio de EXECUTE na procedure.

Como podemos notar as trigger podem ser usadas de forma bem flexível. Com elas podemos gerar mecanismos de segurança mais flexíveis, auditar dados de tabelas e implementar regras de negócios com mais facilidade.