0

Hello everyone am trying to insert data into base tables via a view that joins more than two tables. I have an instead of trigger that should insert the data into the tables, not sure where have gone wrong on the code. The following is the codes have used.

View

CREATE OR REPLACE FORCE VIEW "NEW_APPLICANT" ("FIRST_NAME", "LAST_NAME", "ADDRESS",
     "MATH_SCORE", "ENGLISH_SCORE", "SCIENCE_SCORE") AS 
select APPLICANT.FIRST_NAME as FIRST_NAME,
       APPLICANT.LAST_NAME as LAST_NAME,
       APPLICANT.ADDRESS as ADDRESS,
       RESULT.MATH_SCORE as MATH_SCORE,
       RESULT.ENGLISH_SCORE as ENGLISH_SCORE,
       RESULT.SCIENCE_SCORE as SCIENCE_SCORE 
from   RESULT RESULT, APPLICANT APPLICANT 
where  APPLICANT.APPLICANT_ID=RESULT.APPLICANT_ID
/

Trigger

create or replace TRIGGER application_new_insert
   INSTEAD OF INSERT ON NEW_APPLICANT
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     INSERT INTO APPLICANT
       (FIRST_NAME, LAST_NAME, ADDRESS)
     VALUES (
     :new.FIRST_NAME,
     :new.LAST_NAME,
     :new.ADDRESS);
   INSERT INTO RESULT ( MATH_SCORE, ENGLISH_SCORE, SCIENCE_SCORE)
   VALUES (
     :new.MATH_SCORE,
     :new.ENGLISH_SCORE,
     :new.SCIENCE_SCORE);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END application_new_insert;

Table definitions. Table applicant:

CREATE TABLE "APPLICANT" 
( "APPLICANT_ID" NUMBER NOT NULL ENABLE, 
  "FIRST_NAME" VARCHAR2(45) NOT NULL ENABLE, 
  "LAST_NAME" VARCHAR2(45) NOT NULL ENABLE, 
  "ADDRESS" VARCHAR2(35), 
  CONSTRAINT "PK_APPLICANT_ID" 
    PRIMARY KEY ("APPLICANT_ID") ENABLE 
) / 

CREATE OR REPLACE TRIGGER "TR_APPLICANT" 
before insert on APPLICANT 
for each row 
begin 
  if :NEW.APPLICANT_ID is null then 
    select APPLICANT_ID_SEQ.nextval 
    into :NEW.APPLICANT_ID 
    from dual; 
  end if; 
end; / 

ALTER TRIGGER "TR_APPLICANT" 
  ENABLE / 

Table result:

CREATE TABLE "RESULT" 
( "RESULT_ID" NUMBER NOT NULL ENABLE, 
"APPLICANT_ID" NUMBER NOT NULL ENABLE, 
"MATH_SCORE" NUMBER NOT NULL ENABLE, 
"ENGLISH_SCORE" NUMBER NOT NULL ENABLE, 
"SCIENCE_SCORE" NUMBER NOT NULL ENABLE, 
CONSTRAINT "PK_RESULT_ID" PRIMARY KEY ("RESULT_ID") ENABLE 
) / 

ALTER TABLE "RESULT" 
  ADD CONSTRAINT "FK_RESULT_APPLICANT_ID" 
    FOREIGN KEY ("APPLICANT_ID") 
    REFERENCES "APPLICANT" ("APPLICANT_ID") 
  ENABLE / 

CREATE OR REPLACE TRIGGER "TR_RESULT" 
before insert on RESULT 
for each row 
begin 
  if :NEW.RESULT_ID is null then 
    select RESULT_ID_SEQ.nextval 
    into :NEW.RESULT_ID 
    from dual; 
  end if; 
end; / 

ALTER TRIGGER "TR_RESULT"
  ENABLE / 
  • What error did you get? What is the problem? – Marco Nov 26 '15 at 7:40
  • am getting the ORA-01779: cannot modify a column which maps to a non key-preserved table error – chibwe Nov 26 '15 at 7:45
1

In your view you do not fetch all the key fields from both tables. Oracle needs that to be able to do the update. In your case you do an insert so I suppose that you do not provide the primary keys for both tables. Since you did not gave the structure of both tables I cannot be more specific.

| improve this answer | |
  • ok let me provide the structure of the tables, i have sequences defined for primary key generation and triggers that insert the numbers generated. – chibwe Nov 26 '15 at 7:55
  • CREATE TABLE "APPLICANT" ( "APPLICANT_ID" NUMBER NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(45) NOT NULL ENABLE, "LAST_NAME" VARCHAR2(45) NOT NULL ENABLE, "ADDRESS" VARCHAR2(35), CONSTRAINT "PK_APPLICANT_ID" PRIMARY KEY ("APPLICANT_ID") ENABLE ) / CREATE OR REPLACE TRIGGER "TR_APPLICANT" before insert on APPLICANT for each row begin if :NEW.APPLICANT_ID is null then select APPLICANT_ID_SEQ.nextval into :NEW.APPLICANT_ID from dual; end if; end; / ALTER TRIGGER "TR_APPLICANT" ENABLE / – chibwe Nov 26 '15 at 8:01
  • 1
    @chibwe edit your question, don't post code in comments. – ypercubeᵀᴹ Nov 26 '15 at 8:01
  • CREATE TABLE "RESULT" ( "RESULT_ID" NUMBER NOT NULL ENABLE, "APPLICANT_ID" NUMBER NOT NULL ENABLE, "MATH_SCORE" NUMBER NOT NULL ENABLE, "ENGLISH_SCORE" NUMBER NOT NULL ENABLE, "SCIENCE_SCORE" NUMBER NOT NULL ENABLE, CONSTRAINT "PK_RESULT_ID" PRIMARY KEY ("RESULT_ID") ENABLE ) / ALTER TABLE "RESULT" ADD CONSTRAINT "FK_RESULT_APPLICANT_ID" FOREIGN KEY ("APPLICANT_ID") REFERENCES "APPLICANT" ("APPLICANT_ID") ENABLE / – chibwe Nov 26 '15 at 8:02
  • CREATE OR REPLACE TRIGGER "TR_RESULT" before insert on RESULT for each row begin if :NEW.RESULT_ID is null then select RESULT_ID_SEQ.nextval into :NEW.RESULT_ID from dual; end if; end; / ALTER TRIGGER "TR_RESULT" ENABLE / – chibwe Nov 26 '15 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.