231107 PL/SQL TRIGGER VIEW, 복합뷰

# test table (new_emp)생성

CREATE TABLE hr.new_emp 
AS  
SELECT employee_id, last_name, salary, department_id, email, job_id, hire_date 
FROM hr.employees; 

# test table (new_dept) 생성

CREATE TABLE hr.new_dept 
AS 
SELECT d.department_id, d.department_name, d.location_id, e.tot_dept_sal 
FROM ( SELECT department_id, sum(salary) tot_dept_sal 
       FROM hr.employees 
       GROUP BY department_id ) e, hr.departments d 
WHERE e.department_id = d.department_id ; 

# table 확인

SELECT * FROM hr.new_emp; 
SELECT * FROM hr.new_dept;

단순 view : dml 조작 가능
복합 view 생성 : dml 조작 불허

# 복합 view 생성

CREATE OR REPLACE VIEW emp_details 
AS  
SELECT e.employee_id, e.last_name, e.salary, e.department_id, e.email, e.job_id, e.hire_date, d.department_name, d.location_id, d.tot_dept_sal 
FROM hr.new_emp e, hr.new_dept d --종속관계 
WHERE e.department_id = d.department_id; 

# 원칙상 dml 조작이 불허 -> 가능: trigger 

SELECT * FROM hr.emp_details;

# 복합뷰는 DML 작업을 수행할 수 없다. 
# 복합뷰에 대해서 DML 작업을 수행할 수 있도록 하려면 INSTEAD OF TRIGGER 를 만들어서 해결 할 수 있다.
- VIEW 에서만 사용 : INSTEAD OF TRIGGER

CREATE OR REPLACE TRIGGER emp_dept_trigger 
INSTEAD OF 
    INSERT OR 
    UPDATE OR 
    DELETE ON hr.emp_details 
    FOR EACH ROW 
BEGIN 
    IF INSERTING THEN 
        INSERT INTO hr.new_emp (employee_id, last_name, salary, department_id, email, job_id, hire_date) 
        VALUES (:new.employee_id, :new.last_name, :new.salary, :new.department_id, :new.email, :new.job_id, :new.hire_date); 

        UPDATE hr.new_dept 
        SET tot_dept_sal = tot_dept_sal + :new.salary 
        WHERE department_id = :new.department_id;         
    ELSIF DELETING THEN 

        DELETE FROM hr.new_emp WHERE employee_id = :old.employee_id; 

        UPDATE hr.new_dept 
        SET tot_dept_sal = tot_dept_sal - :old.salary 
        WHERE department_id = :old.department_id; 
    ELSIF UPDATING('SALARY') THEN 

        UPDATE hr.new_emp 
        SET salary = :new.salary 
        WHERE employee_id = :new.employee_id; 

        UPDATE hr.new_dept 
        SET tot_dept_sal = tot_dept_sal + (:new.salary - :old.salary) 
        WHERE department_id = :new.department_id; 
    ELSIF UPDATING('DEPARTMENT_ID') THEN  

        UPDATE hr.new_emp 
        SET department_id = :new.department_id 
        WHERE employee_id = :new.employee_id; 
         
        UPDATE hr.new_dept 
        SET tot_dept_sal = tot_dept_sal - :old.salary 
        WHERE department_id = :old.department_id;  
         
        UPDATE hr.new_dept 
        SET tot_dept_sal = tot_dept_sal + :new.salary 
        WHERE department_id = :new.department_id; 
    END IF; 
END emp_dept_trigger; 
/ 

# insert문 test  해보기

INSERT INTO hr.emp_details (employee_id, last_name, salary, department_id, email, job_id, hire_date) 
VALUES(300, 'ORACLE', 1000, 10, 'ORACLE', 'IT_PROC', SYSDATE); 

SELECT * FROM hr.emp_details; 
SELECT * FROM hr.new_emp WHERE employee_id = 300; 
SELECT * FROM hr.new_dept WHERE department_id = 10; 

# delete문 test 해보기

DELETE FROM hr.emp_details WHERE employee_id = 100; 

SELECT * FROM hr.emp_details; 
SELECT * FROM hr.new_emp; 
SELECT * FROM hr.new_dept;

ROLLBACK;