INSTEAD OF Trigger

- bird, fish, mammal Table,  animal View
- 사용자는 animal View 만 볼 수 있는 권한을 준다.
- View를 통해서 데이타를 insert 해 본다.

 

 

 

CREATE TABLE bird (
 bird_name VARCHAR2(10) NOT NULL PRIMARY KEY,
 weight NUMBER(5,2)
);

 

 

CREATE TABLE fish (
 fish_name VARCHAR(10) NOT NULL PRIMARY KEY,
 weight NUMBER(5,2)
);

 

 

CREATE TABLE mammal (
 mammal_name VARCHAR(10) NOT NULL PRIMARY KEY,
 weight NUMBER(5,2)
);

 

 

CREATE OR REPLACE VIEW animal (animal_kind, animal_name, weight) AS
SELECT 'BIRD', bird_name, weight FROM bird
UNION ALL
SELECT 'FISH', fish_name, weight FROM fish
UNION ALL
SELECT 'MAMMAL', mammal_name, weight FROM mammal;

 

 

INSERT INTO animal VALUES ('BIRD', 'Eagle', 70);
 

 

 

- UNION ALL 연산자를 써서 INSERT가 직접적으로 안된다.

CREATE OR REPLACE Trigger animal_info_insert
INSTEAD OF INSERT ON animal
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
 IF :n.animal_name IS NOT NULL THEN
  IF :n.animal_kind = 'FISH' THEN
   INSERT INTO fish (fish_name, weight) VALUES (:n.animal_name, :n.weight);
  ELSIF :n.animal_kind = 'BIRD' THEN
   INSERT INTO bird (bird_name, weight) VALUES (:n.animal_name, :n.weight);
  ELSIF :n.animal_kind = 'MAMMAL' THEN
   INSERT INTO mammal (mammal_name, weight) VALUES (:n.animal_name, :n.weight);
  END IF;
 END IF;
END;

 

 

INSERT INTO animal VALUES ('BIRD', 'Eagle', 70);
 

 

 

select * from animal;
 

 

 

select * from bird;