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]
Inserting data from a single form in two tables
-
- Moderador
- Posts: 1396
- Joined: Fri, 01 Feb 2008 2:06 pm
- Location: Rio de Janeiro - RJ
- Contact:
Makes a trigger in person insted in person _cy:!:
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.
I tried using Select Max more does not work and error as time.
-
- Moderador
- Posts: 1396
- Joined: Fri, 01 Feb 2008 2:06 pm
- Location: Rio de Janeiro - RJ
- Contact:
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
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
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
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;
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,
vlw,
Anything posts there,
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,
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,
-
- 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
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.
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.
-
- Information
-
Who is online
Users browsing this forum: No registered users and 16 guests