Hi all,
If the ZIPCODE table has no corresponding record, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT table
SQL> CREATE TABLE student
(
zip VARCHAR2(5),
student_id NUMBER,
created_by VARCHAR2(10),
created_date DATE,
modified_by VARCHAR2(10),
modified_date DATE
);
SQL> CREATE TABLE zipcode
(
zip VARCHAR2(5),
zip_id NUMBER,
created_by VARCHAR2(10),
created_date DATE,
modified_by VARCHAR2(10),
modified_date DATE
);
SQL> CREATE VIEW student_v
AS
SELECT *
FROM student;
View created.
SQL> CREATE OR replace TRIGGER student_ins
instead OF INSERT ON student_v
FOR EACH ROW
DECLARE
v_zip VARCHAR2(5);
BEGIN
BEGIN
SELECT zip
INTO v_zip
FROM zipcode
WHERE zip = :NEW.zip;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO zipcode
(zip,
zip_id,
created_by,
created_date,
modified_by,
modified_date)
VALUES (:NEW.zip,
:NEW.zip_id, -->Not commented
USER,
SYSDATE,
USER,
SYSDATE);
END;
INSERT INTO student
(zip,
student_id,
created_by,
created_date,
modified_by,
modified_date)
VALUES (:NEW.zip,
:NEW.student_id,
USER,
SYSDATE,
USER,
SYSDATE);
end;
Waning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER STUDENT_INS:
LINE/COL ERROR
-------- -------------------------------------------
19/21 PLS-00049: bad bind variable 'NEW.ZIP_ID'
SQL> ed
CREATE OR replace TRIGGER student_ins
instead OF INSERT ON student_v
FOR EACH ROW
DECLARE
v_zip VARCHAR2(5);
BEGIN
BEGIN
SELECT zip
INTO v_zip
FROM zipcode
WHERE zip = :NEW.zip;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO zipcode
(zip,
zip_id,
created_by,
created_date,
modified_by,
modified_date)
VALUES (:NEW.zip,
---:NEW.zip_id, --> commented Replace with NULL
NULL,
USER,
SYSDATE,
USER,
SYSDATE);
END;
INSERT INTO student
(zip,
student_id,
created_by,
created_date,
modified_by,
modified_date)
VALUES (:NEW.zip,
:NEW.student_id,
USER,
SYSDATE,
USER,
SYSDATE);
end;
SQL> /
SQL> insert into student_v(zip) values('111');
1 row created.
SQL> select * from student_v
2 /
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
----- ---------- ---------- --------- ---------- ---------
111 APPS 03-APR-14 APPS 03-APR-14
SQL> select * from student
2 /
ZIP STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
----- ---------- ---------- --------- ---------- ---------
111 APPS 03-APR-14 APPS 03-APR-14
SQL> select * from zipcode
2 /
ZIP ZIP_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_
----- ---------- ---------- --------- ---------- ---------
111 APPS 03-APR-14 APPS 03-APR-14