In an oracle procedure, I need to insert data in EmployeeHeader table and then later insert the PK id of this table as the FK id of EmployeeDetails table. How can we achieve this?
INSERT INTO EmployeeHeader(
HEADER_PK_ID
empNo
)
VALUES(
HEADER_PK_ID_SEQ.NEXTVAL,
'SOMETHING'
);
INSERT INTO EmployeeDetails (
DTLHEADER_PK_ID,
HEADER_fK_ID
empname,
age
)
VALUES(
DTLHEADER_PK_ID_SEQ.NEXTVAL,
HEADER_PK_IDn, -- (THIS NEEDS TO BE FETCHED FROM EmployeeHeader)
21
);
Use the RETURNING clause of the INSERT statement:
DECLARE
nHeader_pk_id NUMBER;
BEGIN
INSERT INTO EmployeeHeader
(HEADER_PK_ID, EMPNO)
VALUES
(HEADER_PK_ID_SEQ.NEXTVAL, 'SOMETHING')
RETURNING HEADER_PK_ID INTO nHeader_pk_id;
INSERT INTO EmployeeDetails
(DTLHEADER_PK_ID, HEADER_FK_ID, EMPNAME, AGE)
VALUES
(DTLHEADER_PK_ID_SEQ.NEXTVAL, nHeader_pk_id, 'Somebody', 21);
END;
My personal preference is to use ON INSERT triggers to handle the population of primary key fields, in the following manner:
CREATE OR REPLACE TRIGGER EMPLOYEEHEADER_BI
BEFORE INSERT ON EMPLOYEEHEADER
FOR EACH ROW
BEGIN
IF :NEW.HEADER_PK_ID IS NULL THEN
:NEW.HEADER_PK_ID := HEADER_PK_ID_SEQ.NEXTVAL;
END IF;
END EMPLOYEEHEADER_BI;
CREATE OR REPLACE TRIGGER EMPLOYEEDETAILS_BI
BEFORE INSERT ON EMPLOYEEDETAILS
FOR EACH ROW
BEGIN
IF :NEW.DTLHEADER_PK_ID IS NULL THEN
:NEW.DTLHEADER_PK_ID := DTLHEADER_PK_ID_SEQ.NEXTVAL;
END IF;
END EMPLOYEEDETAILS_BI;
and the INSERT statements become:
DECLARE
nHeader_pk_id NUMBER;
nDtlheader_pk_id NUMBER;
BEGIN
INSERT INTO EmployeeHeader
(EMPNO) -- Note: PK field not mentioned - will be populated by trigger
VALUES
('SOMETHING')
RETURNING HEADER_PK_ID INTO nHeader_pk_id;
INSERT INTO EmployeeDetails
(HEADER_FK_ID, EMPNAME, AGE) -- Note: PK field not mentioned - will be populated by trigger
VALUES
(nHeader_pk_id, 'Somebody', 21)
RETURNING DTLHEADER_PK_ID INTO nDtlheader_pk_id;
END;
(I use the IF pk_field IS NULL THEN construct because I often need to copy data from production to development databases and wish to preserve any key values pulled from production to simplify debugging. If you don't have this requirement you can eliminated the IS NULL check and just assign the sequence's NEXTVAL directly to the column).
Done in this manner the application code doesn't need to know or care about which sequence is used to generate the primary key value for a particular table, and the primary key field is always going to end up populated.
Best of luck.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments