231106 PL/SQL TRIGGER, FOR EACH ROW(행트리거),조건부술어

■ TRIGGER (트리거)
ex) 로그정보, 검색
- 트리거는 데이터베이스에 저장되고 지정된 이벤트에 대한 응답으로 실행되는 PL/SQL 블록이다.
- 오라클 데이터베이스는 지정된 조건이 발생할때 트리거를 자동으로 실행한다. 
- 트리거는 테이블, 뷰, 스키마(소유자), 데이터베이스(모든 유저)에 정의할 수 있다.
- CREATE TRIGGER 시스템 권한 필요하다. 익명블록구조이다.

# 트리거 유형 ex) 대차대조표
- 특정 테이블, 뷰에 INSERT, UPDATE, DELETE 문 
- CREATE, ALTER, DROP 문 
- 데이터베이스 시작 또는 종료
- 특정 오류 메시지 또는 임의의 오류 메시지 
- 유저 로그인, 로그오프

<HR SESSION>;
# 트리거 권한 확인 = 'CREATE TRIGGER'

SELECT * FROM session_privs WHERE privilege = 'CREATE TRIGGER';

# 권한 회수(트리거)

DROP TABLE hr.dept PURGE;

# 트리거성 

CREATE TABLE hr.dept AS SELECT * FROM hr.employees;

SELECT * FROM user_triggers ;
SELECT trigger_body FROM user_triggers ;

DROP TABLE hr.dept PURGE;
CREATE TABLE hr.dept AS SELECT * FROM hr.departments;

<< DML TRIGGER >>
1) BEFORE 문장 트리거

CREATE OR REPLACE TRIGGER dept_before 
BEFORE
    INSERT ON hr.dept
BEGIN
    dbms_output.put_line('INSERT하기 전에 문장 트리거 수행');
END dept_befor;
/

2) AFTER 문장 트리거

CREATE OR REPLACE TRIGGER dept_after
AFTER
    INSERT ON hr.dept
BEGIN 
    dbms_output.put_line('INSERT한 후에 문장 트리거 수행');
END dept_after;
/

3) BEFORE 행 트리거 : 시점
# FOR EACH ROW : 행 트리거, 영향을 받은 행이 있는 경우 수행되는 트리거, 값에 대한 감사를 할때(급여)

CREATE OR REPLACE TRIGGER dept_row_before 
BEFORE
    INSERT ON hr.dept
    FOR EACH ROW
BEGIN
    dbms_output.put_line('INSERT하기 전에 행 트리거 수행');
END dept_befor;
/

4) AFTER 행 트리거

CREATE OR REPLACE TRIGGER dept_row_after
AFTER
    INSERT ON hr.dept
    FOR EACH ROW
BEGIN 
    dbms_output.put_line('INSERT한 후에 행 트리거 수행');
END dept_after;
/

 

 

INSERT INTO hr.dept (department_id, department_name, manager_id, location_id)
VALUES (300, 'Data Arcitect', 100, 1500);

# 조건부술어 
- INSERTING, UPDATING, DELETIN 을 사용할 수 있다.

# TEST TABLE 생성

CREATE TABLE hr.copy_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees;

CREATE OR REPLACE TRIGGER test_trig 
BEFORE
    -- 한 개의 컬럼을 지정할 수 있다.
    DELETE OR INSERT OR UPDATE OF salary ON hr.copy_emp
    FOR EACH ROW -- 행트리거에서만 사용
    -- when 조건절, 
    -- 1) new, old 에는 :을 쓰지 않는다. 2) new+old = UPDATE, new : INSERT, old : DELETE
    WHEN(new.department_id = 20 OR old.department_id = 10)
DECLARE
    salary_diff NUMBER;
BEGIN 
    IF DELETING THEN -- DELETE 
        DBMS_OUTPUT.PUT_LINE('OLD SALARY : '||:old.salary);
    ELSIF INSERTING THEN -- INSERT
        DBMS_OUTPUT.PUT_LINE('NEW SALARY : '||:new.salary);
    ELSE -- UPDATE
        salary_diff := :new.salary - :old.salary;
        DBMS_OUTPUT.PUT_LINE('ID : '||:new.employee_id||' OLD SALARY : '||:old.salary||' NEW SALARY : '||:new.salary||'  Difference Of Salary : '||salary_diff);
    END IF;
END test_trig;
/

SELECT * FROM user_triggers;

UPDATE hr.copy_emp
SET salary = salary*1.1
WHERE department_id = 20;

UPDATE hr.copy_emp
SET salary = salary*1.1
WHERE department_id = 10;

UPDATE hr.copy_emp
SET salary = salary*1.1
WHERE department_id = 30;

-- 트리거 실행

INSERT INTO hr.copy_emp(employee_id, last_name, salary, department_id)
VALUES(300, 'ORACLE', 1000, 20);

-- 트리거 발생 X

INSERT INTO hr.copy_emp(employee_id, last_name, salary, department_id)
VALUES(400, 'HADOOP', 2000, 10);

-- 트리거 발생

DELETE FROM hr.copy_emp WHERE department_id = 10;

-- 트리거 발생 X

DELETE FROM hr.copy_emp WHERE department_id = 20;

-- 트리거 발생

DELETE FROM hr.copy_emp WHERE employee_id = 200;

 

-- 트리거 발생 X

DELETE FROM hr.copy_emp WHERE employee_id = 100;