Inserting data from a single form in two tables

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Try000
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 15
Joined: Thu, 23 Sep 2010 9:36 am

I need to make a form with the fields of the person and person _cy. The _cy person table is a complement of the person table that has already created from another place.
My difficulty and write this data Insert the data into the person and person _cy person at the same time, the code (nrseqperson) as soon as generated has to be stored in the ID_SOOA, from the person_cy table.
[/b]
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

Makes a trigger in person insted in person _cy:!:
Try000
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 15
Joined: Thu, 23 Sep 2010 9:36 am

The problem and I have to pick up the code (nrseqpessoa) created in the person table and pass to ID_person of the person table_cy because the table_cy table and a complement of the person table so the code can not be different.
I tried using Select Max more does not work and error as time.
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

Post your tables (structure) ai ..

to good practice says that we should use sequence to control the ID of a table ..

this problem that you It's lifting and easy to react just use variable: new inside the trigger and you will have the ID that should be inserted in the person_cy table
Try000
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 15
Joined: Thu, 23 Sep 2010 9:36 am

I made this code here more I'vê changed so much that I'm confused I'm starting and I know very little I still created this procedure

Select all

  
 
 
 
 
CREATE OR REPLACE PROCEDURE pessoa_upsert( 
			p_NRSEQPESSOA		IN  NUMBER(12,0) 
			p_NRSEQEMPRESA	IN  NUMBER(12,0) 
			p_CDSITUACAO		IN  VARCHAR2(8) 
			p_TPPESSOA		IN  VARCHAR2(8) 
			p_TXNOME		        IN  VARCHAR2(60)	 
			p_AORECEBER		IN  VARCHAR2(3)	 
			p_AOPAGAR		IN  VARCHAR2(3)	 
			p_TXLOGRADOURO		IN  VARCHAR2(60) 
			p_TXNUMERO		IN  VARCHAR2(10)	 
			p_TXCIDADE		IN  VARCHAR2(30)	 
			p_TXESTADO		IN  VARCHAR2(20)	 
			p_TXCOMPLEMENTOENDERECO	IN  VARCHAR2(30)	 
			p_NRCEP	         	IN  VARCHAR2(20)	 
			p_TXTELEFONE		IN  VARCHAR2(15)	 
			p_TXEMAIL		        IN  VARCHAR2(50)	 
			p_DTCLIENTEDESDE	IN  DATE	 
			p_TXPAIS		        IN  VARCHAR2(30)	 
			p_NRDOCUMENTO	IN  VARCHAR2(30) 
        
 
) 
IS 
BEGIN 
    UPDATE pessoa 
      SET NRSEQPESSOA	=   p_NRSEQPESSOA,		 
	  NRSEQEMPRESA	        =   p_NRSEQEMPRESA,	 
          CDSITUACAO		=   p_CDSITUACAO, 
	  TPPESSOA		=   p_TPPESSOA,	 
	  TXNOME		        =   p_TXNOME,		 
	  AORECEBER		=   p_AORECEBER, 
	  AOPAGAR		        =   p_AOPAGAR, 
	  TXLOGRADOURO	=   p_TXLOGRADOURO,	 
	  TXNUMERO		=   p_TXNUMERO,	 
   	  TXCIDADE		=   p_TXCIDADE,	 
	  TXESTADO		=   p_TXESTADO,	 
	  TXCOMPLEMENTOENDERECO =   p_TXCOMPLEMENTOENDERECO,	 
  	  NRCEP		        =   p_NRCEP,		 
	  TXTELEFONE		=   p_TXTELEFONE, 
	  TXEMAIL		        =   p_TXEMAIL,		 
   	  DTCLIENTEDESDE	=   p_DTCLIENTEDESDE,	 
   	  TXPAIS		        =   p_TXPAIS,	 
   	  NRDOCUMENTO		=   p_NRDOCUMENTO	 
        WHERE nrseqpessoa    =   p_nrseqpessoa; 
    
    UPDATE pessoa_cyber 
      SET  ID_PESSOA                   =  p_ID_PESSOA 
	   ID_UNIDADE_CADASTRO   =  p_ID_UNIDADE_CADASTRO	 
	   ID_CARGO                      =  p_ID_CARGO	 
	   DT_CADASTRO                =  p_DT_CADASTRO 
	   DT_NASCIMENTO             =  p_DT_NASCIMENTO	 
	   TXSEXO                          =  p_TXSEXO	 
	   NRCELULAR                    =  p_NRCELULAR 
	   NRNIVEL_ESCOLARIDADE  =  p_NRNIVEL_ESCOLARIDADE 
	   TXESCOLA                      =  p_TXESCOLA	 
	   TXOBS                           =  p_TXOBS 
 
         WHERE ID_PESSOA            =  p_ID_PESSOA; 
 
    IF SQL%NOTFOUND THEN 
      
INSERT INTO pessoa( NRSEQPESSOA,		 
		    NRSEQEMPRESA,		 
		    CDSITUACAO,		 
		    TPPESSOA,		 
		    TXNOME,		 
		    AORECEBER,		 
		    AOPAGAR,		 
		    TXLOGRADOURO,		 
		    TXNUMERO,			 
		    TXCIDADE,			 
		    TXESTADO,			 
		    TXCOMPLEMENTOENDERECO,		 
		    NRCEP,	         		 
		    TXTELEFONE,			 
		    TXEMAIL,			 
		    DTCLIENTEDESDE,		 
		    TXPAIS,			 
		    NRDOCUMENTO 
) 
VALUES(p_nrseqpessoa, 
       p_cdsituacao, 
       p_tppessoa, 
       p_txnome, 
       p_aoreceber, 
       p_aopagar, 
       p_txlogradouro, 
       p_txnumero, 
       p_txcomplementoendereco, 
       p_nrcep, 
       p_txcidade, 
       p_txestado, 
       p_txtelefone, 
       p_txemail, 
       p_dtclientedesde, 
       p_txpais, 
       p_nrdocumento); 
 
  IF ID_PESSOA IS NULL THEN 
  INSERT INTO pessoa_cyber (id_pessoa) 
            values (p_nrseqpessoa); 
  END IF; 
  IF P_NM_FANTASIA IS NULL THEN 
     RAISE_APPLICATION_ERROR(-20001,'VALOR INVÁLIDO '); 
  END IF; 
            
INSERT INTO pessoa_cyber(ID_PESSOA,	 
			 ID_UNIDADE_CADASTRO,	 
			 ID_CARGO,	 
			 DT_CADASTRO,	 
			 DT_NASCIMENTO,	 
			 TXSEXO, 
			 NRCELULAR, 
			 NRNIVEL_ESCOLARIDADE, 
			 TXESCOLA,	 
			 TXOBS) 
VALUES (ID_PESSOA,	 
	ID_UNIDADE_CADASTRO,	 
	ID_CARGO,	 
	DT_CADASTRO,	 
	DT_NASCIMENTO,	 
	TXSEXO, 
	NRCELULAR, 
	NRNIVEL_ESCOLARIDADE, 
	TXESCOLA,	 
	TXOBS); 
 
 
    END IF; 
 
 EXCEPTION 
          WHEN OTHERS THEN 
          RAISE_APPLICATION_ERROR(-20001,' ACONTECU UM PROBLEMA AO INSERIR CLIENTE NA TABELA LOC_CLIENTE'); 
        END; 
 
 
 
 
 
 
ederphil
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 52
Joined: Wed, 15 Oct 2008 4:05 pm
Location: taquara-RS

Maicon,

This type of control you can do via the interface you will use for the data entry, do not need to reinvent the wheel, if it is via Oracle form, it already does this for You do not have to do this control in your hand, just create a Master-Detail block, but of course a trigger would also solve your case, but it is kind of ingenious because of the other fields of the person_cyber, Masssss is just a tip, follow an example of trigger,

Select all

CREATE OR REPLACE TRIGGER nome_da_trigger 
AFTER INSERT OR UPDATE OR DELETE ON pessoa 
FOR EACH ROW                     
-- 
DECLARE 
  -- 
  id_unidade_cadastro    pessoa_cyber%id_unidade_cadastro  ; 
  id_cargo               pessoa_cyber%id_cargo             ; 
  dt_cadastro            pessoa_cyber%dt_cadastro          ; 
  dt_nascimento          pessoa_cyber%dt_nascimento        ; 
  txsexo                 pessoa_cyber%txsexo               ; 
  nrcelular              pessoa_cyber%nrcelular            ; 
  nrnivel_escolaridade   pessoa_cyber%nrnivel_escolaridade ; 
  txescola               pessoa_cyber%txescola             ; 
  txobs                  pessoa_cyber%txobs                ; 
BEGIN 
  -- 
  IF INSERTING THEN 
    --Código do Insert 
    INSERT INTO pessoa_cyber( id_pessoa 
                            , id_unidade_cadastro 
                            , id_cargo 
                            , dt_cadastro 
                            , dt_nascimento 
                            , txsexo 
                            , nrcelular 
                            , nrnivel_escolaridade 
                            , txescola 
                            , txobs)  
                      VALUES( :NEW.id_pessoa 
                            , id_unidade_cadastro 
                            , id_cargo 
                            , dt_cadastro 
                            , dt_nascimento 
                            , txsexo 
                            , nrcelular 
                            , nrnivel_escolaridade 
                            , txescola 
                            , txobs);  
  --                    
  ELSIF UPDATING THEN 
    --Codigo do Update 
    NULL; 
    -- 
  ELSIF DELETING THEN 
    --Codigo do Delete 
    --Considerando que não foi feito ON DELETE CASCADE na criação da tabela pessoa_cyber e não tenha chave com pessoa. 
    NULL; 
    -- 
  END IF; 
  -- 
END; 
/ 

vlw,
Anything posts there,
SergioLBJr
Rank: Oracle Guru
Rank: Oracle Guru
Posts: 448
Joined: Tue, 16 Jun 2009 3:07 pm
Location: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

It's expensive if a record of the person table has several of the person_cy it is best to use a detail master block, which it will make all controls that you need.

You can do this via trigger of forms as well, because when creating a trigger at the bank you have to be very careful not to insert records in unwanted situations, if it is something more specific I suggest you do in same forms.

Any doubts of which trigger to use, specifically specify your situation.
burga
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Thu, 26 Nov 2009 1:05 pm
Location: SP

Study the INSERT ALL command that resolves your problem ... and in the doinsert all query you select the sequence plus the values ??to be entered ...
Try000
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 15
Joined: Thu, 23 Sep 2010 9:36 am

I tried otherwise and it worked out a lot thanks for the tip ...
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 16 guests