Database Trigger

 

As triggers assim como as stored procedures são armazenadas do banco de dados e podem ser compostas de instruções SQL e PL/SQL. Entretanto, stored procedure e triggers diferem na forma como estes são acionados. Uma stored procedure é explicitamente acionda por um usuário, aplicação ou trigger. As triggers são implicitamente disparadas pelo Oracle quando um determinado evento ocorre. O disparo da trigger independe do usuário ou aplicação que gerou o evento.

  
        Uma database trigger é subprograma associado a uma tabela, view ou evento. A traigger pode ser acionada uma vez quando um determinado evento ocorre ou várias vezes para cada linha afetada por uma instrução INSERT, UPDATE ou DELETE. A trigger pode ser aciondad após um determindo evento para registrá-lo ou efetuar alguma atividade posterior, ou pode ser acionado antes de um evento para previnir operações indevidas ou ajustar os novos dados para que estes estejam de acordo com a regra de negócio.
 
         Como principais motivos para o uso de database trigger, podemos citar os seguintes:

 

·         geração automática de valores de colunas derivados;

·         prevenção de transações inválidas;

·         reforçar regras de negócio complexas;

·         prover auditoria;

·         gerar estatísticas sobre acesso às tabelas;

·         prover log de transações.

 

É escopo deste artigo apenas as triggers associadas a tabelas, no entanto, existem as INSTED OF triggers voltadas para DML’s disparados contra objetos view e triggers de eventos de sistema voltadas para atividade de administração do banco de dados.

 

Elementos

 

Antes de se codificar uma trigger é interessante decidir, segundo as necessidades de regra de negócio, quais os elementos desta futura trigger.

 

Tabela 1. Elementos triggers

Componentes

Descrição

Valores

Tempo

Quando o trigger dispara em relação ao evento de acionamento (DML)

·       BEFORE

·       AFTER

Evento de acionamento

Quais operações de manipulação de tabela (DML) disparam a trigger

·       INSERT

·       UPDATE

·       DELETE

abrangência da trigger

Quantas vezes o corpo da trigger será executado

·       de linha (for each row)

·       de instrução(*)

Corpo da trigger

Que ações serão executadas

Bloco PL/SQL

(*) Opção default

 

Quanto à quantidade de vezes que a trigger será acionda, podemos afirmar que o comportamento default das por instrução e quando o DML acionador de trigger afeta apenas uma linha, tanto o trigger de instrução quanto o trigger de linha dispararão apenas uma vez. Ao passo que, quando o DML acionado afeta várias linhas o trigger de instrução será executado apenas uma vez enquanto que o trigger de linha será executado na mesma quantidade das linhas afetadas.

 

 

SQL>CREATE OR REPLACE TRIGGER jobs_biud

  2    BEFORE INSERT OR UPDATE OR DELETE ON jobs

  3  BEGIN

  4    IF TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18' THEN

  5     RAISE_APPLICATION_ERROR(-20205,'Alterações são permitidas apenas no horário de expediente');

  6    END IF;

  7  END jobs_biud;

 

Exemplo 1 – Trigger
 Obs: O exemplo 1 é uma trigger que será disparada apenas uma vez quando um INSERT, UPDATE ou DELETE for efetuado na tabela jobs.

 

 Predicado Condicional

 

Quando programamos uma trigger para vários eventos e temos a necessidade de identifica qual evento disparou a trigger, poderemos usar os predicados condicionais que são funções booleanas que podem ser utilizadas para determinar a operação que disparou o trigger.

 

 

SQL>CREATE OR REPLACE TRIGGER employees_biud

  2  BEFORE INSERT OR UPDATE OR DELETE ON employees

  3  BEGIN

  4    IF (TO_CHAR (SYSDATE,'HH24') NOT BETWEEN '08' AND '18') THEN

  5      IF DELETING THEN

  6       RAISE_APPLICATION_ERROR(-20502,'Deleções na tabela de empregados apenas no horario normal');

  7      ELSIF INSERTING THEN

  8        RAISE_APPLICATION_ERROR(-20502,'Inserções na tabela de empregados apenas no horario normal');

  9      ELSIF UPDATING('SALARY') THEN

 10     RAISE_APPLICATION_ERROR(-20502,'Alterações no salário apenas no horario normal');

 11      ELSE

 12        RAISE_APPLICATION_ERROR(-20504,'Alterações nos empregados apenas no horario normal');

 13      END IF;

 14    END IF;

 15  END employees_biud;

 

Exemplo 2 – Predicado

 

Trigger de Linha

 

Uma trigger de linha é disparada uma vez para cada linha afetada pela instrução DML. Uma trigger de linha é identificada pela cláusula FOR EACH ROW.

 

Qualificadores (:new, :old)

Em uma trigger de linha, existe uma forma de acessar os valores dos campos que estão sendo processados atualmente, atraves dos identificadores :new, :old. O compilador PL/SQL irá tratá-los como tabela_da_trigger%ROWTYPE.

 

 

SQL>CREATE OR REPLACE TRIGGER employees_biur

  2    BEFORE  INSERT OR UPDATE ON employees

  3      FOR EACH ROW

  4      BEGIN

  5        IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP')) THEN

  6          IF :NEW.salary > 15000 THEN

  7            RAISE_APPLICATION_ERROR (-20202,'Este empregado não pode receber este valor');

  8          END IF;

  9       END IF;

 10  END employees_biur;

 

Exemplo 3 – Qualificadores
                                        Tabela 2 – Qualificadores

DML

:old

:new

INSERT

NULO

Valores Novos

DELETE

Valores antigos

NULO

UPDATE

Valores antigos

Valores Novos

 

 

SQL>create table dept_audit

  2  (userid          varchar2(30)

  3  ,timestamp       date

  4  ,tipo_dml        CHAR(1)

  5  ,old_dept_id     NUMBER

  6  ,old_name        varchar2(30)

  7  ,old_manager_id  number

  8  ,old_location_id number

  9  ,new_dept_id     NUMBER

 10  ,new_name        varchar2(30)

 11  ,new_manager_id  number

 12  ,new_location_id number

 13  )

 

SQL>CREATE OR REPLACE TRIGGER department_aiudr

  2    AFTER INSERT OR UPDATE OR DELETE ON departments

  3      FOR EACH ROW

  4  DECLARE

  5    v_DML dept_audit.tipo_dml%TYPE;

  6  BEGIN

  7    IF INSERTING THEN

  8      v_DML := 'I';

  9    ELSIF DELETING THEN

 10      v_DML := 'D';

 11    ELSIF UPDATING THEN

 12      v_DML := 'U';

 13    END IF;

 14 

 15    INSERT INTO dept_audit

 16      (userid              , timestamp            , tipo_dml

 17      ,old_dept_id         , old_name             , old_manager_id

 18      ,old_location_id     , new_dept_id          , new_name

 19      ,new_manager_id      , new_location_id

 20      )

 21    VALUES

 22      (USER                , SYSDATE              , v_DML

 23      ,:OLD.department_id  ,:OLD.department_name  ,:OLD.manager_id

 24      ,:OLD.location_id    ,:NEW.department_id    ,:NEW.department_name

 25      ,:NEW.manager_id     ,:NEW.location_id

 26      );

 27  END;

 

Exemplo 4 – Auditagem por trigger

 

Obs.: Os qualificadores :old, :new, estão disponíveis apenas nas triggers de linha.

 

 

SQL>CREATE OR REPLACE TRIGGER regions_bir

  2    BEFORE INSERT ON regions

  3      FOR EACH ROW

  4  DECLARE

  5    v_region_id regions.region_id%TYPE;

  6  BEGIN

  7    SELECT MAX(region_id)+1

  8      INTO v_region_id

  9      FROM regions;

 10   

 11    :NEW.region_id := v_region_id;

 12  END regions_bir;

 

Exemplo 5 – Autoincremento.

 

         Cláusula WHEN

Nas triggers de linha, podemos restringir a ação da trigger segundo uma condição, onde a mesma será disparada apenas para as linhas que satisfaçam a condição prevista.

 

 

SQL>CREATE OR REPLACE TRIGGER derive_commission_pct

  2    BEFORE INSERT OR UPDATE OF salary ON employees

  3    FOR EACH ROW

  4    WHEN (NEW.job_id = 'SA_REP')

  5  BEGIN

  6    IF INSERTING THEN

  7       :NEW.commission_pct := 0;

  8    ELSIF :OLD.commission_pct IS NULL THEN

  9       :NEW.commission_pct := 0;

 10    ELSE

 11      :NEW.commission_pct := :OLD.commission_pct + 0.05;

 12    END IF;

 13  END;

 14  /

 

Exemplo 6 – Cláusula WHEN

 

No exemplo 6 além da cláusula condicional, percebemos na linha 2 cláusula OF seguida de um campo (salary) da tabela (employees) associada à trigger. Isso indica que a trigger só será disparada quando o update afetar a coluna indicada. Caso a cláusula OF estiver omitida a trigger do exemplo 6 será disparada indepedente da coluna afetada.

 
          Como próximo item de estudo e referentes ao assunto Database Trigger indicamos
  • INSTED OF TRIGGERS voltadas para DML’s disparados contra objetos view
  • triggers de eventos de sistema voltadas para atividade de administração do banco de dados.
 
Espero que as informações tenham sido úteis para o trabalho com Database Triggers no Oracle 
 
Até o próximo artigo !!!
Obrigado !!!