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.
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 /
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
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 /
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 /