트리거는 변경된 테이블명을 계속 참조하지만
펑션, 프로시저, 시노님은 에러 발생(이름이 변경되어서 참조 오브젝트가 없어 compile 에러발생)
테스트 환경 구성
-- 부서 테이블 생성
CREATE TABLE sk.departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(100)
)
0 row(s) modified.
-- 직원 테이블에 부서 외래키 추가
CREATE TABLE sk.employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
hire_date DATE,
salary NUMBER(8, 2),
department_id NUMBER,
CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES sk.departments(department_id) -- 외래키 제약조건
)
0 row(s) modified.
-- 부서 테이블에 샘플 데이터 삽입
INSERT INTO sk.departments (department_id, department_name) VALUES (10, 'Sales')
1 row(s) modified.
> INSERT INTO sk.departments (department_id, department_name) VALUES (20, 'HR')
1 row(s) modified.
> INSERT INTO sk.departments (department_id, department_name) VALUES (30, 'IT')
1 row(s) modified.
> INSERT INTO sk.departments (department_id, department_name) VALUES (40, 'Finance')
1 row(s) modified.
> COMMIT
0 row(s) modified.
> -- 직원 테이블에 샘플 데이터 삽입
INSERT INTO sk.employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 5000, 10)
1 row(s) modified.
> INSERT INTO sk.employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', TO_DATE('2022-07-20', 'YYYY-MM-DD'), 6000, 20)
1 row(s) modified.
> INSERT INTO sk.employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (3, 'Alice', 'Johnson', 'alice.johnson@example.com', TO_DATE('2021-03-01', 'YYYY-MM-DD'), 7000, 30)
1 row(s) modified.
> INSERT INTO sk.employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (4, 'Bob', 'Brown', 'bob.brown@example.com', TO_DATE('2020-10-05', 'YYYY-MM-DD'), 5500, 10)
1 row(s) modified.
> INSERT INTO sk.employees (employee_id, first_name, last_name, email, hire_date, salary, department_id)
VALUES (5, 'Charlie', 'Davis', 'charlie.davis@example.com', TO_DATE('2019-05-23', 'YYYY-MM-DD'), 4500, 40)
1 row(s) modified.
> COMMIT
0 row(s) modified.
-- 펑션 생성
> CREATE OR REPLACE FUNCTION sk.calculate_annual_salary(p_employee_id NUMBER)
RETURN NUMBER
IS
v_annual_salary NUMBER(12, 2);
BEGIN
-- 해당 직원의 월급을 조회하고 12개월 기준 연봉을 계산
SELECT salary * 12 INTO v_annual_salary
FROM employees
WHERE employee_id = p_employee_id;
RETURN v_annual_salary;
END;
0 row(s) modified.
-- 트리거 생성
> CREATE OR REPLACE TRIGGER sk.check_salary_before_insert_update
BEFORE INSERT OR UPDATE ON sk.employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
0 row(s) modified.
-- 프로시저 생성
> CREATE OR REPLACE PROCEDURE sk.insert_employee (
p_employee_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2,
p_email VARCHAR2,
p_hire_date DATE,
p_salary NUMBER
)
IS
BEGIN
-- 직원 정보 삽입
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_hire_date, p_salary);
-- 커밋
COMMIT;
END;
0 row(s) modified.
-- 퍼블릭 시노님 생성
> CREATE PUBLIC SYNONYM emp FOR sk.employees
0 row(s) modified.
> SELECT object_name, object_type, status
FROM DBA_objects
WHERE owner = 'SK'
OBJECT_NAME |OBJECT_TYPE|STATUS|
---------------------------------+-----------+------+
CALCULATE_ANNUAL_SALARY |FUNCTION |VALID |
INSERT_EMPLOYEE |PROCEDURE |VALID |
SYS_C007604 |INDEX |VALID |
CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |VALID |
DEPARTMENTS |TABLE |VALID |
EMPLOYEES |TABLE |VALID |
SYS_C007605 |INDEX |VALID |
7 row(s) fetched.
> select *
from DBA_DEPENDENCIES
where REFERENCED_NAME = 'EMPLOYEES'
and referenced_owner = 'SK'
OWNER |NAME |TYPE |REFERENCED_OWNER|REFERENCED_NAME|REFERENCED_TYPE|REFERENCED_LINK_NAME|DEPENDENCY_TYPE|
------+---------------------------------+---------+----------------+---------------+---------------+--------------------+---------------+
PUBLIC|EMP |SYNONYM |SK |EMPLOYEES |TABLE | |HARD |
SK |INSERT_EMPLOYEE |PROCEDURE|SK |EMPLOYEES |TABLE | |HARD |
SK |CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |SK |EMPLOYEES |TABLE | |HARD |
SK |CALCULATE_ANNUAL_SALARY |FUNCTION |SK |EMPLOYEES |TABLE | |HARD |
4 row(s) fetched.
> SELECT * FROM sk.EMPLOYEES
EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |HIRE_DATE |SALARY|DEPARTMENT_ID|
-----------+----------+---------+-------------------------+-----------------------+------+-------------+
1|John |Doe |john.doe@example.com |2023-01-15 00:00:00.000| 5000| 10|
2|Jane |Smith |jane.smith@example.com |2022-07-20 00:00:00.000| 6000| 20|
3|Alice |Johnson |alice.johnson@example.com|2021-03-01 00:00:00.000| 7000| 30|
4|Bob |Brown |bob.brown@example.com |2020-10-05 00:00:00.000| 5500| 10|
5|Charlie |Davis |charlie.davis@example.com|2019-05-23 00:00:00.000| 4500| 40|
5 row(s) fetched.
> SELECT * FROM sk.DEPARTMENTS
DEPARTMENT_ID|DEPARTMENT_NAME|
-------------+---------------+
10|Sales |
20|HR |
30|IT |
40|Finance |
4 row(s) fetched.
EMPLOYEES 테이블 rename 수행
-- rename 수행
> ALTER TABLE sk.EMPLOYEES RENAME TO EMPLOYEES_RE
0 row(s) modified.
-- 관련 오브젝트 상태 invalid 변경됨
> SELECT object_name, object_type, status
FROM DBA_objects
WHERE owner = 'SK'
OBJECT_NAME |OBJECT_TYPE|STATUS |
---------------------------------+-----------+-------+
CALCULATE_ANNUAL_SALARY |FUNCTION |INVALID|
INSERT_EMPLOYEE |PROCEDURE |INVALID|
SYS_C007604 |INDEX |VALID |
CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |INVALID|
DEPARTMENTS |TABLE |VALID |
SYS_C007605 |INDEX |VALID |
EMPLOYEES_RE |TABLE |VALID |
7 row(s) fetched.
> select *
from DBA_DEPENDENCIES
where REFERENCED_NAME = 'EMPLOYEES_RE'
and referenced_owner = 'SK'
OWNER |NAME |TYPE |REFERENCED_OWNER|REFERENCED_NAME|REFERENCED_TYPE|REFERENCED_LINK_NAME|DEPENDENCY_TYPE|
------+---------------------------------+---------+----------------+---------------+---------------+--------------------+---------------+
PUBLIC|EMP |SYNONYM |SK |EMPLOYEES_RE |TABLE | |HARD |
SK |CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |SK |EMPLOYEES_RE |TABLE | |HARD |
SK |CALCULATE_ANNUAL_SALARY |FUNCTION |SK |EMPLOYEES_RE |TABLE | |HARD |
SK |INSERT_EMPLOYEE |PROCEDURE|SK |EMPLOYEES_RE |TABLE | |HARD |
4 row(s) fetched.
-- compile 진행
> ALTER FUNCTION SK.CALCULATE_ANNUAL_SALARY COMPILE
0 row(s) modified.
> ALTER PUBLIC SYNONYM EMP COMPILE
0 row(s) modified.
> ALTER TRIGGER SK.CHECK_SALARY_BEFORE_INSERT_UPDATE COMPILE
0 row(s) modified.
> ALTER PROCEDURE SK.INSERT_EMPLOYEE COMPILE
0 row(s) modified.
-- 트리거 제외 다른 오브젝트 종속 오브젝트에서 사라짐
> SELECT LPAD(' ', 4*(LEVEL-1))|| DECODE (LEVEL, 1, OWNER || '.' || OBJECT_NAME, OWNER || '.' || OBJECT_NAME) AS OBJECT_NAME
, OBJECT_TYPE, STATUS
,(SELECT OWNER||'.'||OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID = REFERENCED_OBJECT_ID) REFER_OBJ_NAME
, 'ALTER '||DECODE(OBJECT_TYPE,'SYNONYM',DECODE(OWNER,'PUBLIC','PUBLIC '))||
DECODE(OBJECT_TYPE,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)||' '||
DECODE(OBJECT_TYPE,'SYNONYM',CASE WHEN OWNER <> 'PUBLIC' THEN OWNER||'.' ELSE ' 'END,OWNER||'.')||
OBJECT_NAME||' COMPILE'|| DECODE(OBJECT_TYPE,'PACKAGE BODY',' BODY')||';' COMPILE_CMD
FROM (SELECT R.OBJECT_ID, R.REFERENCED_OBJECT_ID, O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, O.STATUS
FROM PUBLIC_DEPENDENCY R, SYS.DBA_OBJECTS O
WHERE O.OBJECT_ID = R.OBJECT_ID )
START WITH REFERENCED_OBJECT_ID = (SELECT OBJECT_ID FROM SYS.DBA_OBJECTS
WHERE OWNER = 'SK'
AND OBJECT_NAME = 'EMPLOYEES_RE')
CONNECT BY PRIOR OBJECT_ID = REFERENCED_OBJECT_ID
OBJECT_NAME |OBJECT_TYPE|STATUS|REFER_OBJ_NAME |COMPILE_CMD |
------------------------------------+-----------+------+---------------+-----------------------------------------------------------+
SK.CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |VALID |SK.EMPLOYEES_RE|ALTER TRIGGER SK.CHECK_SALARY_BEFORE_INSERT_UPDATE COMPILE;|
1 row(s) fetched.
-- 오브젝트 invalid 상태, 테이블명이 변경되었기 때문에 compile error
> SELECT object_name, object_type, status
FROM DBA_objects
WHERE owner = 'SK'
OBJECT_NAME |OBJECT_TYPE|STATUS |
---------------------------------+-----------+-------+
CALCULATE_ANNUAL_SALARY |FUNCTION |INVALID|
EMPLOYEES_RE |TABLE |VALID |
INSERT_EMPLOYEE |PROCEDURE |INVALID|
SYS_C007604 |INDEX |VALID |
CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |VALID |
DEPARTMENTS |TABLE |VALID |
SYS_C007605 |INDEX |VALID |
7 row(s) fetched.
테이블명 원상복구
-- 테이블명 원복
> ALTER TABLE sk.EMPLOYEES_RE RENAME TO EMPLOYEES
0 row(s) modified.
> SELECT object_name, object_type, status
FROM DBA_objects
WHERE owner = 'SK'
OBJECT_NAME |OBJECT_TYPE|STATUS |
---------------------------------+-----------+-------+
CALCULATE_ANNUAL_SALARY |FUNCTION |INVALID|
INSERT_EMPLOYEE |PROCEDURE |INVALID|
SYS_C007604 |INDEX |VALID |
CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |INVALID|
DEPARTMENTS |TABLE |VALID |
EMPLOYEES |TABLE |VALID |
SYS_C007605 |INDEX |VALID |
7 row(s) fetched.
> select *
from DBA_DEPENDENCIES
where REFERENCED_NAME = 'EMPLOYEES'
and referenced_owner = 'SK'
OWNER|NAME |TYPE |REFERENCED_OWNER|REFERENCED_NAME|REFERENCED_TYPE|REFERENCED_LINK_NAME|DEPENDENCY_TYPE|
-----+---------------------------------+-------+----------------+---------------+---------------+--------------------+---------------+
SK |CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER|SK |EMPLOYEES |TABLE | |HARD |
1 row(s) fetched.
-- compile 수행
> ALTER FUNCTION SK.CALCULATE_ANNUAL_SALARY COMPILE
0 row(s) modified.
> ALTER PUBLIC SYNONYM EMP COMPILE
0 row(s) modified.
> ALTER TRIGGER SK.CHECK_SALARY_BEFORE_INSERT_UPDATE COMPILE
0 row(s) modified.
> ALTER PROCEDURE SK.INSERT_EMPLOYEE COMPILE
0 row(s) modified.
-- 테이블명 원복 후 참조 오브젝트 상태
> select *
from DBA_DEPENDENCIES
where REFERENCED_NAME = 'EMPLOYEES'
and referenced_owner = 'SK'
OWNER |NAME |TYPE |REFERENCED_OWNER|REFERENCED_NAME|REFERENCED_TYPE|REFERENCED_LINK_NAME|DEPENDENCY_TYPE|
------+---------------------------------+---------+----------------+---------------+---------------+--------------------+---------------+
PUBLIC|EMP |SYNONYM |SK |EMPLOYEES |TABLE | |HARD |
SK |CALCULATE_ANNUAL_SALARY |FUNCTION |SK |EMPLOYEES |TABLE | |HARD |
SK |CHECK_SALARY_BEFORE_INSERT_UPDATE|TRIGGER |SK |EMPLOYEES |TABLE | |HARD |
SK |INSERT_EMPLOYEE |PROCEDURE|SK |EMPLOYEES |TABLE | |HARD |
4 row(s) fetched.
댓글
댓글 쓰기