Assign value trigger variables through a SELECT

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
gracianow
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Wed, 08 Oct 2008 3:57 pm
Location: São Paulo -SP
Wagner Graciano
Developer | Designer
Sistemas Web | Criação e Reformulação de Websites
INTRANET | EXTRANET
Skype: gracianow
messenger: gracianow@msn.com

Aolá Friends I am a beginner and I need your help to assign value the variables of a trigger through the last record of the last record of another table. My duvina is in how I will assign the value to each of the variables and where I position the SELECT in the code:

Select all

 
Create Or Replace Trigger Srp_Atualiza_Movm_PRONTUARIO 
After Insert ON SRP_MOVM_REQUISICAO  
For Each Row 
 
Declare 
 
V_Dt_Requisicao Date; 
V_Fk_Cd_Prontuario Number; 
V_Fk_Cd_Recurso Number; 
V_Tp_Prontuario Number; 
V_Tp_Solicitacao_Pront Number; 
 
Begin 
 
Insert Into Srp_Movm_Prontuario (Id_Movm_Prontuario, Fk_Cd_Prontuario, Fk_Id_Requisicao, Fk_Cd_Paciente, Tp_Prontuario, Tp_Solicitacao, Dt_Requisicao) 
Values (Seq.Id_Movm_Prontuario.NEXTVAL, V_Fk_Cd_Prontuario, :NEW.ID_REQUISICAO, V_Fk_Cd_Prontuario, V_Tp_Prontuario, V_Tp_Solicitacao_Pront, SYSDATE); 
 
End; 
 
can help me?
diegolenhardt
Moderador
Moderador
Posts: 1177
Joined: Thu, 15 Oct 2009 10:28 am
Location: Recife

First, it is not possible to reference the table with Select / Insert / Update / Delete within the trigger of the table itself ...

will receive mutant table error ...
gracianow
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Wed, 08 Oct 2008 3:57 pm
Location: São Paulo -SP
Wagner Graciano
Developer | Designer
Sistemas Web | Criação e Reformulação de Websites
INTRANET | EXTRANET
Skype: gracianow
messenger: gracianow@msn.com

Hello, the table I refer to would be another table and not the same.
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

But these values ??you want to pick up on the Select are from the table itself? If it is you will fall into the familiar error "Table Is Mutating" ...

By kindness, give a read in the topic below, where this was discussed Long: http://en.glufke.net/oracle/viewtopic.p ... c&start=25
gracianow
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Wed, 08 Oct 2008 3:57 pm
Location: São Paulo -SP
Wagner Graciano
Developer | Designer
Sistemas Web | Criação e Reformulação de Websites
INTRANET | EXTRANET
Skype: gracianow
messenger: gracianow@msn.com

Thanks for the obeservations; But what is at issue is that I need popular a Base B table with the data that will be inserted into another table that has the same fields in the base A. The fields are the same but from different tables and distinct users.
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
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

When you say from another base, do you mean only from another Owner or another database, through DB Link?

If it is for DB LINK there the conversation changes and becomes a distributed transaction.

If it is only replicating in another table the same data in the table that is already being inserted, just include the: New. In Insert Values. If this is not what you want you would need to explain which tables and rows you need to get this data. The ideal is to post an example that we can see and run, with example structure of tables and data and showing logic than you want to happen when line X is inserted.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 1 guest