Oracle – Trigger
Boa tarde pessoal,
Como foi o final de semana? Deu para descansar um pouco? Espero que sim! Vamos retomar nossos estudos?
Você ja ouviu falar em “gatilho do Banco de dados” ou mais conhecido como triggers?
Então vamos lá:
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á executada. 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/SQLOnde:
- 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_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.
Fonte
Abraços galera!