--------------------------------------------------------------------------------
-- 'INSTEAD OF' Triggers

-- Use INSTEAD OF triggers to perform DELETE, UPDATE, or INSERT operations on views, which are not inherently modifiable. "The View Query" for a list of constructs that prevent inserts, updates, or deletes on a view.
--------------------------------------------------------------------------------


-- Example

-- Student data is stored in two tables:
-- students424 and students430.
CREATE TABLE students424 
(
  sid    VARCHAR(9),
  grade  NUMBER 
);
CREATE TABLE students430 
(
  sid    VARCHAR(9),
  grade  NUMBER
);



-- all_students is a view,
-- such that all_students = students424 UNION students430.
CREATE VIEW all_students(sid, grade, classNo) 
AS SELECT sid, grade, '424' FROM students424
   UNION ALL
   SELECT sid, grade, '430' FROM students430;


-- Create an INSTEAD OF trigger for insertion
CREATE OR REPLACE TRIGGER insertStudents 
INSTEAD OF INSERT ON all_students
FOR EACH ROW
BEGIN
  IF :new.classNo = '424'
  THEN
    INSERT INTO students424 VALUES(:new.sid, :new.grade);
  ELSIF :new.classNo = '430'
  THEN
    INSERT INTO students430 VALUES(:new.sid, :new.grade);
  ELSE
    RAISE_APPLICATION_ERROR(-20001, 'Class number not found');
  END IF;
END;


-- Now, we can do "insert" on a view:
INSERT INTO all_students VALUES('123456789', 90, '424');
INSERT INTO all_students VALUES('111111111', 80, '430');
INSERT INTO all_students VALUES('222222222', 70, '456');


-- Check the results
SELECT * FROM students424;
SELECT * FROM students430;
SELECT * FROM all_students;