251117 TIL

TRIGGER(Cont.)

DML 작업 복제

테이블 생성

-- source 테이블
CREATE TABLE hr.emp_target
(id number,
name varchar2(30),
day timestamp default systimestamp,
sal number)
TABLESPACE users;

-- target 테이블
CREATE TABLE hr.emp_source
(id number,
name varchar2(30),
day timestamp default systimestamp,
sal number)
TABLESPACE users;

트리거 생성

CREATE OR REPLACE TRIGGER emp_copy_trigger
AFTER
INSERT OR DELETE OR UPDATE ON hr.emp_source
FOR EACH ROW
BEGIN
    IF inserting THEN
        INSERT INTO hr.emp_target(id, name, day, sal) VALUES(:new.id, :new.name, :new.day, :new.sal);
    ELSIF deleting THEN
        DELETE FROM hr.emp_target WHERE id = :old.id;
    ELSIF updating('sal') THEN
        UPDATE hr.emp_target
        SET sal = :new.sal
        WHERE id = :old.id;
    ELSIF updating('name') THEN
        UPDATE hr.emp_target
        SET name = :new.name
        WHERE id = :old.id;
    END IF;
END emp_copy_trigger;
/

INSERT

INSERT INTO hr.emp_source(id, name, day, sal) VALUES(100, 'ORA1', default, 1000);

SELECT s.id s_id, s.name s_name, s.day s_day, s.sal s_sal, t.id t_id, t.name t_name, t.day t_day, t.sal t_sal
FROM emp_source s, emp_target t
WHERE s.id = t.id
AND s.id = 100;

UPDATE

UPDATE hr.emp_source
SET sal = 2000
WHERE id = 100;

SELECT s.id s_id, s.name s_name, s.day s_day, s.sal s_sal, t.id t_id, t.name t_name, t.day t_day, t.sal t_sal
FROM emp_source s, emp_target t
WHERE s.id = t.id
AND s.id = 100;

UPDATE hr.emp_source
SET name = 'ORACLE'
WHERE id = 100;

SELECT s.id s_id, s.name s_name, s.day s_day, s.sal s_sal, t.id t_id, t.name t_name, t.day t_day, t.sal t_sal
FROM emp_source s, emp_target t
WHERE s.id = t.id
AND s.id = 100;

DELETE

DELETE FROM hr.emp_source WHERE id = 100;

SELECT s.id s_id, s.name s_name, s.day s_day, s.sal s_sal, t.id t_id, t.name t_name, t.day t_day, t.sal t_sal
FROM emp_source s, emp_target t
WHERE s.id = t.id
AND s.id = 100;

오라클 복제 솔루션 GoldenGate

실시간 데이터 메시 플랫폼 관리형 서비스

• CDC(Change Data Capture)

트리거 소스 코드 안에 TCL 사용 X

• 트리거를 호출한 DML 작업까지 영향이 미침
• 트리거 안에 TCL 문이 있을 경우 DML 실행 시 오류 발생
  • ORA-04092: cannot COMMIT in a trigger
• 트리거 안에 있는 트랜잭션을 독립적으로 사용할 수 있도록 하려면 PRAGMA AUTONOMOUS_TRANSACTION(9i) 사용

CREATE OR REPLACE TRIGGER emp_copy_trigger
AFTER
INSERT OR DELETE OR UPDATE ON hr.emp_source
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
END;
/

값에 대한 감사 구현

테이블 생성

CREATE TABLE hr.audit_emp_sal(
    name varchar2(30),
    day timestamp,
    id number,
    old_sal number,
    new_sal number
) TABLESPACE users;

DROP TABLE hr.emp PURGE;

CREATE TABLE hr.emp
AS
SELECT employee_id id, salary sal, job_id job, department_id dept_id
FROM hr.employees;

프로시저 생성

CREATE OR REPLACE PROCEDURE hr.update_proc(p_id IN number)
IS
BEGIN
    UPDATE hr.emp
    SET sal = sal * 1.1
    WHERE id = p_id;
END update_proc;
/

트리거 생성

CREATE OR REPLACE TRIGGER emp_sal_audit
AFTER
UPDATE OF sal ON hr.emp
FOR EACH ROW
BEGIN
    IF :old.sal != :new.sal THEN
        INSERT INTO hr.audit_emp_sal(name, day, id, old_sal, new_sal) VALUES(user, systimestamp, :new.id, :old.sal, :new.sal);
    END IF;
END emp_sal_audit;
/

실행 결과

SELECT sal FROM hr.emp WHERE id = 100; -- 24000
SELECT * FROM hr.audit_emp_sal; -- no data

exec update_proc(100)

SELECT * FROM hr.emp WHERE id = 100; -- 26400
SELECT * FROM hr.audit_emp_sal;

anna에게 프로시저 실행 권한 부여

GRANT execute ON hr.update_proc TO anna;

anna에서 프로시저 실행

exec hr.update_proc(200);

감사 테이블 조회

SELECT * FROM hr.audit_emp_sal;

복합뷰 DML을 위한 트리거

• 단순뷰를 통한 DML 작업은 가능하지만, 복합뷰를 통해서는 불가
• 트리거 필요

테이블 생성

DROP TABLE hr.emp PURGE;

CREATE TABLE hr.emp
AS
SELECT employee_id id, salary sal, department_id dept_id
FROM hr.employees;

DROP TABLE hr.dept PURGE;

CREATE TABLE hr.dept
AS
SELECT d.department_id dept_id, d.department_name dept_name, sum(e.salary) total_sal
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;

뷰 생성

CREATE OR REPLACE VIEW hr.emp_details
AS
SELECT e.id, e.sal, e.dept_id, d.dept_name, d.total_sal
FROM hr.emp e, hr.dept d
WHERE e.dept_id = d.dept_id;

복합 뷰를 통한 DML시 오류 발생

INSERT INTO hr.emp_details(id, sal, dept_id, dept_name) VALUES(300, 2000, 60, 'IT');
-- 01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"

INSTEAD OF TRIGGER

• 뷰에 생성하는 트리거
• 복합뷰는 DML작업을 수행할 수 없음. 가능하도록 만들려면 INSTEAD OF TRIGGER를 생성하면 됨
• 뷰에 트리거는 행 트리거만 사용해야 함

CREATE OR REPLACE TRIGGER hr.emp_details_trigger
INSTEAD OF
INSERT OR UPDATE OR DELETE ON hr.emp_details
FOR EACH ROW
BEGIN
    IF inserting THEN
        INSERT INTO hr.emp(id, sal, dept_id) VALUES(:new.id, :new.sal, :new.dept_id);

        UPDATE hr.dept
        SET total_sal = total_sal + :new.sal
        WHERE dept_id = :new.dept_id;
    ELSIF updating('sal') THEN
        UPDATE hr.emp
        SET sal = :new.sal
        WHERE id = :old.id;

        UPDATE hr.dept
        SET total_sal  = total_sal + (:new.sal - :old.sal)
        WHERE dept_id = :old.dept_id;
    ELSIF updating('dept_id') THEN
        UPDATE hr.emp
        SET dept_id = :new.dept_id
        WHERE id = :old.id;

        UPDATE hr.dept
        SET total_sal  = total_sal - :old.sal
        WHERE dept_id = :old.dept_id;

        UPDATE hr.dept
        SET total_sal  = total_sal + :new.sal
        WHERE dept_id = :new.dept_id;
    ELSIF deleting THEN
        DElETE FROM hr.emp WHERE id = :old.id; 

        UPDATE hr.dept
        SET total_sal = total_sal - :old.sal
        WHERE dept_id = :old.dept_id;
    END IF;
END emp_details_trigger;
/

INSERT

INSERT INTO hr.emp_details(id, sal, dept_id, dept_name) VALUES(300, 2000, 60, 'IT');

SELECT * FROM hr.emp_details;

UPDATE

UPDATE hr.emp_details
SET sal = 2000
WHERE id = 200;

SELECT * FROM hr.emp_details WHERE id = 200;

UPDATE hr.emp_details
SET dept_id = 20
WHERE id = 200;

SELECT * FROM hr.emp_details WHERE dept_id in (10, 20);

DELETE

DELETE FROM hr.emp_details WHERE id = 201;

SELECT * FROM hr.emp_details WHERE dept_id in (20);

DDL TRIGGER

• SYS 유저가 생성
• 테이블을 DROP, TRUNCATE를 수행할 수 있는 유저
  • 테이블 소유자
  • DROP ANY TABLE 시스템 권한이 있는 유저

데이터베이스 레벨

• 데이터베이스 레벨에서 drop table, truncate table 수행을 불허해야 할 때

-- SYS 세션에서 트리거 생성
CREATE OR REPLACE TRIGGER no_drop_truncate
BEFORE
DROP OR TRUNCATE ON DATABASE
BEGIN
    RAISE_APPLICATION_ERROR(-20000, 'DROP or TRUNCATE 할 수 없습니다.');
END no_drop_truncate;
/

-- HR 세션
DROP TABLE hr.dept PURGE;
-- ORA-20000: DROP or TRUNCATE 할 수 없습니다.

TRUNCATE TABLE hr.dept;
-- ORA-20000: DROP or TRUNCATE 할 수 없습니다.

-- SYS 세션
DROP TABLE hr.dept PURGE;
-- ORA-20000: DROP or TRUNCATE 할 수 없습니다.

TRUNCATE TABLE hr.dept;
-- ORA-20000: DROP or TRUNCATE 할 수 없습니다.

• 테이블을 삭제해야 할 때는 no_drop_truncate 트리거 삭제 후 테이블 삭제

DROP TRIGGER no_drop_truncate;

스키마(유저) 레벨

• 스키마(유저) 레벨에서 drop table, truncate table 수행을 불허해야 할 때

CREATE OR REPLACE TRIGGER hr_drop_truncate
BEFORE
DROP OR TRUNCATE ON hr.schema
BEGIN
    RAISE_APPLICATION_ERROR(-20000, 'DROP or TRUNCATE할 수 없습니다.');
END hr_drop_truncate;
/

• hr 유저만 테이블 삭제 불가

-- HR 세션
DROP TABLE hr.dept PURGE;
-- ORA-20000: DROP or TRUNCATE 할 수 없습니다.

TRUNCATE TABLE hr.dept;
-- ORA-20000: DROP or TRUNCATE 할 수 없습니다.

-- SYS 세션
TRUNCATE TABLE hr.emp;
-- Table HR.EMP이(가) 잘렸습니다.

DROP TABLE hr.emp PURGE;
-- Table HR.EMP이(가) 삭제되었습니다.

DDL문 감사

테이블 생성

CREATE TABLE sys.ddl_obj_log(
    ddl_user varchar2(30),
    obj_user varchar2(30),
    obj_name varchar2(30),
    obj_type varchar2(30),
    ddl_time timestamp,
    sql_text varchar2(100)
)TABLESPACE users;

트리거 생성

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE
CREATE OR ALTER OR DROP OR TRUNCATE ON DATABASE
DECLARE
    sql_text ora_name_list_t;
    n pls_integer;
    v_stmt varchar2(100);
BEGIN
    n := ora_sql_txt(sql_text);

    FOR i IN 1..n LOOP
        v_stmt := v_stmt || sql_text(i);
    END LOOP;

    INSERT INTO sys.ddl_obj_log(ddl_user, obj_user, obj_name, obj_type, ddl_time, sql_text) VALUES (ora_login_user, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, systimestamp, v_stmt);
END ddl_trigger;
/

• System-Defined Event Attribute Function : ora_login_user, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type

Database PL/SQL Language Reference

DDL문 감사 로그 테이블 조회

SELECT * FROM ddl_obj_log;