[Hint] COMPOUND/Composite Trigger TRIGGER Example

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Thu, 27 Aug 2009 12:00 pm

Oracle 11 g brings a new feacture called \"Compound Trigger\", i.e. Trigger Composed.
A practical example of its use is to avoid the famous ORA-04091 (Table is mutating) and that is the example that I will present.

Code: Select all
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_Nao
      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;
here beleza, however a person can only have one primary address set. Therefore, it is necessary to read the address table to do this validation before inserting or updating your data, which would cause the error ORA-04091 mutant table.

Solution:
Code: Select all
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;
/
Note: this example can also be used in previous versions to 11 g, however it will take 2 Triggers and an external variable so that it is accessible by the 2 Triggers.

Code: Select all

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]

is that there
stohlirck
Location: Porto Alegre - RS

Tiago Stöhlirck

Poston Mon, 31 Aug 2009 12:56 pm

Very cool. :D You can add a trigger only 4 types (before/after statement, before/after each row). Pity that much legacy in 10 g and behind with implementations with those packages to store session data of the modified rows. Let's face it, was something inconvenient, a workaround that lasted many years and that most people do as a cake recipe, without understanding what is happening.
fsitja
Location: Gaúcho no Rio de Janeiro - RJ

"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Poston Tue, 01 Sep 2009 9:30 pm

I found it interesting, do some tests and any questions I post here: wink:
jessica.ff
Location: Gravataí - RS

ninguém é tão sabio que não tenha a aprender, e nem tão ignorante que não tenha a ensinar.


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: Google Adsense [Bot] and 10 guests