Comandos para montar uma Trigger

[marilainecsergio]

Por favor, preciso saber como eu crio uma Trigger em um b.dados Oracle.

Obrigada,

Marilaine

[Trevisolli]

Olá Marilaine, tudo bem?

Dê uma procurada aqui no fórum mesmo, por Trigger, que irá exemplos interessantes como este:

http://www.glufke.net/oracle/viewtopic. ... ht=trigger


Qualquer coisa manda pra gente.

[dr_gori]

É uma pergunta bem ampla. Existem vários tipos de trigger. Depende de qual tipo você quer criar.

Mas uma coisa é certa: Se você não sabe como criar uma trigger, provavelmente você também não sabe a diferença entre elas. Ou seja, tem grande chance de você estar criando uma trigger errada.

Lembrando que triggers afetam o comportamento do banco, das regras de negócio... Sugiro você ler mais a respeito de triggers antes de criar.

[bertosro]

http://glufke.net/oracle/viewtopic.php?t=2052

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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 é:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

SELECT trigger_name
 FROM user_triggers;

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

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

DROP TRIGGER nome_da_trigger;

Caso prefira apenas desabilitar a trigger use o comando:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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.

Selecionar tudo

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.

Selecionar tudo

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:

Selecionar tudo

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:

Selecionar tudo

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

Selecionar tudo

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:

Selecionar tudo

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.

BERTOSRO [/code]

[dr_gori]

Show !!!

[Raimundo]

Cara, gostei muito da explicação, se possível me indique um 'lugar' onde possa encontrar outras explicações desse nível, forte abraço!

[fsitja]

A melhor fonte de informações é a documentação da Oracle. As explicações são bem completas, atualizadas para a versão 11g e contém exemplos para testar.
O capítulo 22 é todo sobre triggers e contêm desde conceitos até implementação, e links para os demais assuntos relacionados.

http://download.oracle.com/docs/cd/B283 ... iggers.htm