[Dica] Exemplo COMPOUND TRIGGER / Trigger Composta

[stohlirck]

O Oracle 11g traz uma nova feacture chamada "Compound Trigger", ou seja, Trigger Composta.
Um exemplo prático de seu uso é evitar o famoso ORA-04091 (Table is mutating) e é esse o exemplo que vou apresentar.

Selecionar tudo

Create Table Enderecos 
(
  Id_Endereco  Number(4)  Not Null,
  Id_Pessoa  Number(4)  Not Null,
  Endereco_Completo  Varchar2(200)  Not Null,
  Endereco_Principal  Varchar2(1)  Not Null
    Constraint Ck_Sim_não
      Check (Endereco_Principal In ('S', 'N'))
);

Alter Table Enderecos
  Add Constraint Pk_End Primary Key (Id_Endereco);
  
  
Insert Into Enderecos (Id_Endereco, Id_Pessoa, Endereco_Completo, Endereco_Principal) Values (1, 1, 'Porto Alegre', 'S');
Insert Into Enderecos (Id_Endereco, Id_Pessoa, Endereco_Completo, Endereco_Principal) Values (2, 1, 'Selbach', 'N');
Commit;

Até aqui beleza, porém uma pessoa só pode ter um Endereço Principal setado. Sendo assim, é preciso ler a tabela de Endereços para fazer essa validação antes de inserir ou atualizar seus dados, o que causaria o erro ORA-04091 de tabela mutante.

Solução:

Selecionar tudo

Create Or Replace Trigger TRG_IU_ENDERECOS
For Insert Or Update On ENDERECOS
COMPOUND TRIGGER

Type Typ_IdPessoa Is Table Of ENDERECOS.ID_PESSOA%Type
     Index By Binary_Integer;

V_IdPessoa Typ_IdPessoa;


AFTER EACH ROW IS 
  V_Ind Pls_Integer;
Begin   
   V_Ind := V_IdPessoa.Count + 1;
   V_IdPessoa(V_Ind) := :New.Id_Pessoa;   
END AFTER EACH ROW;


AFTER STATEMENT IS   
  Vl_Count Pls_Integer;    
Begin   
   For V_Ind In Nvl(V_IdPessoa.First, 1) .. Nvl(V_IdPessoa.Last, 0)   
   Loop   
      Select Count(1)
        Into Vl_Count
		  From Enderecos e
		 Where e.Id_Pessoa = V_IdPessoa(V_Ind)
		   And e.Endereco_Principal = 'S';
		   --		   
		   If Vl_Count > 1 Then		   
		     Raise_Application_Error(-20000, 'Erro: Apenas um Endereço Principal deve ser setado. Pessoa [' || V_IdPessoa(V_Ind) || ']');		     
		   End If;		   
		   --
   End Loop;   
END AFTER STATEMENT;


End TRG_IU_ENDERECOS;
/

Obs.: Esse Exemplo também pode ser usado em versões anteriores ao 11g, porém serão necessárias 2 Triggers e uma variavel externa para que seja acessivel pelas 2 Triggers.

Selecionar tudo

Insert Into Enderecos (Id_Endereco, Id_Pessoa, Endereco_Completo, Endereco_Principal) Values (3, 2, 'Ibirubá / RS', 'S');
Insert Into Enderecos (Id_Endereco, Id_Pessoa, Endereco_Completo, Endereco_Principal) Values (4, 3, 'Tapera / RS', 'S');
Commit;

Insert Into Enderecos (Id_Endereco, Id_Pessoa, Endereco_Completo, Endereco_Principal) Values (5, 2, 'Cruz Alta / RS', 'S');
 
ORA-20000: Erro: Apenas um Endereço Principal deve ser setado. Pessoa [2]

Update Enderecos set Endereco_Principal = 'S';
 
ORA-20000: Erro: Apenas um Endereço Principal deve ser setado. Pessoa [1]

É isso ai..

[fsitja]

Muito bacana isso. Dá para juntar numa trigger só os 4 tipos (before/after statement, before/after each row). Pena que tem muito legado em 10g e para trás com implementações com aquelas packages para guardar os dados de sessão das linhas modificadas. Convenhamos, era algo inconveniente, um workaround que durou muitos anos e que a maioria faz como uma receita de bolo, sem entender o que está acontecendo.

[jessica.ff]

Achei interessante, fazer uns testes e qualquer duvida eu posto por aqui