Assign value trigger variables through a SELECT

[gracianow]

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]

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]

Hello, the table I refer to would be another table and not the same.

[fsitja]

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]

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.

[fsitja]

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.