231107 PL/SQL DML TRIGGER, DML ROW TRIGGER

<< DML 문장 트리거 >>
영향을 받은 행이 전혀 없더라도 무조건 한 번 수행되는 트리거

# test table 생성

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

CREATE OR REPLACE TRIGGER secure_emp
BEFORE --타이밍 설정(필수)
    -- DML 중 하나는 필수적으로 작성
    INSERT OR 
    DELETE OR
    UPDATE ON hr.emp 
BEGIN
    IF to_char(sysdate, 'dy') IN ('월', '화') OR to_char(sysdate, 'hh24:mi') BETWEEN '09:30' AND '10:00' THEN
        -- TRUE : 조건) 월, 화 또는 오전 09:30-10:00
        RAISE_APPLICATION_ERROR(-20000, '작업 시간이 아닙니다.');  -- DML이 돌아가는 session
    END IF;
END secure_emp;
/

# trigger 확인

SELECT * FROM user_triggers WHERE trigger_name = 'SECURE_EMP';

<< DML 시간 및 요일에 제한 test >>

INSERT INTO hr.emp VALUES (300, 'ORACLE', 1000, 10);

DELETE FROM hr.emp WHERE dept_id = 20;

UPDATE hr.emp
SET sal = sal*1.1
WHERE id = 200;

# 설정하기

CREATE OR REPLACE TRIGGER secure_emp
BEFORE 
    INSERT OR 
    DELETE OR
    UPDATE ON hr.emp 
BEGIN
    IF to_char(sysdate, 'dy') IN ('월', '화') OR to_char(sysdate, 'hh24:mi') BETWEEN '10:00' AND '10:30' THEN
        -- 조건: 월, 화  또는 오전 10:00-10:30 
        IF INSERTING THEN 
            RAISE_APPLICATION_ERROR(-20000, 'INSERT 작업 시간이 아닙니다.');  
        ELSIF DELETING THEN 
            RAISE_APPLICATION_ERROR(-20001, 'DELETE 작업 시간이 아닙니다.');  
        ELSIF UPDATING('SAL') THEN -- 특정 컬럼 조건
            RAISE_APPLICATION_ERROR(-20002, '급여 수정 작업 시간이 아닙니다.');  
        ELSE
            RAISE_APPLICATION_ERROR(-20003, '수정 작업 시간이 아닙니다.');  
        END IF;
        
    END IF;
END secure_emp;
/

# trigger  확인

SELECT * FROM user_triggers WHERE trigger_name = 'SECURE_EMP';

< RAISE_APPLICATION_ERROR >

INSERT INTO hr.emp (id, name, sal, dept_id)
VALUES (300, 'ORACLE', 1000, 10);

DELETE FROM hr.emp 
WHERE dept_id = 20;

UPDATE hr.emp
SET sal = sal*1.1
WHERE id = 200;

UPDATE hr.emp 
SET dept_id = dept_id+1
WHERE dept_id = 90;

<< DML row trigger(행 트리거) >>
- FOR EACH ROW 절
- 트리거 영향을 받은 각 행에 대해서 한 번 실행된다.
- 영향을 받은 행이 없으면 트리거는 수행되지 않는다.


- DROP TABLE 하면 TRIGGER 종속관계로 인해서 secure_emp 는 확인불가.(자동삭제)
- 실무: 영향도 평가(테이블간의 관계)

DROP TABLE hr.emp PURGE;

# test table 생성

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

-- :new job, :new sal 활용

CREATE OR REPLACE TRIGGER retrict_salary
BEFORE
    INSERT OR -- new
    UPDATE OF sal ON hr.emp -- new, old // DELETE : old 
    FOR EACH ROW
BEGIN
    IF NOT (:new.job IN ('AD_PRES', 'AD_VP')) AND (:new.sal > 15000) THEN
        RAISE_APPLICATION_ERROR(-20000, 'Employee cannot earn more then $15,000');
    END IF;
END retrict_salary;
/

-- 트리거 실행 + 트랜잭션 진행

INSERT INTO hr.emp (id, name, sal, job, dept_id)
VALUES (300, 'ORACLE', 1000,'IT_PROG', 10);

-- 트리거 실행X + 트랜잭션 진행X

INSERT INTO hr.emp (id, name, sal, job, dept_id)
VALUES (400, 'HADOOP', 20000,'IT_PROG', 20);

-- 트리거 실행X + 트랜잭션 진행X

UPDATE hr.emp
SET sal = 30000
WHERE id = 200;

# table 확인

SELECT * FROM hr.emp WHERE id = 300;
SELECT * FROM hr.emp;

-- 익명블록 구조(프로그램)로 진행시, 하나라도 에러발생하면 자동 rollback된다.

BEGIN
    INSERT INTO hr.emp (id, name, sal, job, dept_id)
    VALUES (300, 'ORACLE', 1000,'IT_PROG', 10);
    
    INSERT INTO hr.emp (id, name, sal, job, dept_id)
    VALUES (400, 'HADOOP', 20000,'IT_PROG', 20);
END;
/

# 행 트리거내에서 old, new 수식자를 사용해서 데이터 변경 이전과 이후 값을 참조할 수 있다. 
- BEGIN 절 안에서 old, new 수식자 앞에 콜론(:) 접두어로 붙여야 한다. 
- WHEN 절 에서는 old, new 수식자 앞에 콜론(:) 접두어로 붙이면 안된다.

	old	new
INSERT 문	NULL	새로운 값
UPDATE 문	갱신하기 전의 값	갱신한 후의 값
DELETE 문	삭제 전의 값	NULL

 

WHERE 절	old	new
BEGIN 절	:old	:new

# test table 생성 

CREATE TABLE hr.emp_target (
    id    NUMBER,
    name  VARCHAR2(30),
    day   TIMESTAMP DEFAULT systimestamp,
    sal   NUMBER
);

CREATE TABLE hr.emp_source (
    id    NUMBER,
    name  VARCHAR2(30),
    day   TIMESTAMP DEFAULT systimestamp,
    sal   NUMBER
);

 

# trigger 생성

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;
/

 

# test data

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

SELECT * FROM hr.emp_source;
SELECT * FROM hr.emp_target;
COMMIT;

DELETE FROM hr.emp_target WHERE id = 100;

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

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

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

SELECT * FROM hr.emp_source;
SELECT * FROM hr.emp_target;