| DROP TABLE hr.emp PURGE; /* 테이블 생성 */ CREATE TABLE hr.emp AS SELECT employee_id id, last_name name, salary sal, department_id dept_id FROM hr.employees; |
<< DML 문장 트리거 >>
영향을 받은 행이 전혀 없더라도 무조건 한번 수행되는 트리거 -- 있든 없든 무조건!!
| /* 요일 형식 체크 */ select to_char(sysdate,'dy') from dual; CREATE OR REPLACE TRIGGER secure_emp /* 타이밍 필수 BEFORE or AFTER */ BEFORE /* 해당 테이블의 DML 작업을 하는 모든 SESSION에서 무조건 작동 */ INSERT OR UPDATE OR DELETE ON hr.emp BEGIN /* 특정 요일 또는 특정 시간대에 에러 발생 */ IF to_char(sysdate,'dy') IN ('월','화') OR to_char(sysdate,'hh24:mi') BETWEEN '09:30' AND '10:00' THEN RAISE_APPLICATION_ERROR(-20000,'작업 시간이 아닙니다.'); END IF; END; / /* 트리거 생성 확인 */ SELECT * FROM user_triggers; |
| INSERT INTO hr.emp (id,name,sal,dept_id) VALUES (300,'ORACLE',1000,10); -- ORA-20000: 작업 시간이 아닙니다. DELETE FROM hr.emp WHERE dept_id = 20; -- ORA-20000: 작업 시간이 아닙니다. UPDATE hr.emp SET sal = sal * 1.1 WHERE id = 200; -- ORA-20000: 작업 시간이 아닙니다. |
| CREATE OR REPLACE TRIGGER secure_emp /* 타이밍 필수 BEFORE or AFTER */ BEFORE /* 해당 테이블의 DML 작업을 하는 모든 SESSION에서 무조건 작동 */ INSERT OR UPDATE OR DELETE ON hr.emp BEGIN /* 특정 요일 또는 특정 시간대에 에러 발생 */ IF to_char(sysdate,'dy') IN ('월','화') OR to_char(sysdate,'hh24:mi') BETWEEN '09:30' AND '10:00' THEN /* 작업별 에러메세지 적용*/ IF INSERTING THEN RAISE_APPLICATION_ERROR(-20000,'INSERT 작업 시간이 아닙니다.'); ELSIF DELETING THEN RAISE_APPLICATION_ERROR(-20001,'DELETE 작업 시간이 아닙니다.'); /* UPDATE는 행 또는 특정 칼럼 지정 가능 */ ELSIF UPDATING('SAL') THEN RAISE_APPLICATION_ERROR(-20002,'급여수정 작업 시간이 아닙니다.'); ELSE RAISE_APPLICATION_ERROR(-20003,'수정 작업 시간이 아닙니다.'); END IF; END IF; END; / |
| INSERT INTO hr.emp (id,name,sal,dept_id) VALUES (300,'ORACLE',1000,10); -- ORA-20000: INSERT 작업 시간이 아닙니다. DELETE FROM hr.emp WHERE dept_id = 20; -- ORA-20001: DELETE 작업 시간이 아닙니다. UPDATE hr.emp SET sal = sal * 1.1 WHERE id = 200; -- ORA-20002: 급여수정 작업 시간이 아닙니다. |
# RAISE_APPLICATION_ERROR()
강제 비정상 종료이기 때문에 ROLLBACK 발생
| DROP TABLE hr.emp PURGE; -- 테이블 삭제와 함께 종속된 트리거도 같이 삭제 된다. /* 테이블 생성 */ CREATE TABLE hr.emp AS SELECT employee_id id, last_name name, salary sal, job_id job, department_id dept_id FROM hr.employees; |
<< DML 행 트리거 >>
- FOR EACH ROW 절
- 영향을 받은 각 행에 대해서 한번 실행된다. -- 각 행 마다!
- 영향을 받은 행이 없으면 트리거는 수행되지 않습니다.
| CREATE OR REPLACE TRIGGER restrict_alary BEFORE INSERT OR UPDATE OF sal ON hr.emp FOR EACH ROW BEGIN IF :new.job NOT IN ('AD_PRES','AD_VP') AND :new.sal > 15000 THEN RAISE_APPLICATION_ERROR(-20000,'Employee cannot earn more then $15,000'); END IF; END; / |
| INSERT INTO hr.emp(id,name,sal,job,dept_id) VALUES (300,'ORACLE',1000,'IT_PROG',10); -- 조건에 FALSE >> 저장 INSERT INTO hr.emp(id,name,sal,job,dept_id) VALUES (400,'HADOOP',20000,'IT_PROG',20); -- ORA-20000: Employee cannot earn more then $15,000 UPDATE hr.emp SET sal = 30000 WHERE id = 200; -- ORA-20000: Employee cannot earn more then $15,000 -- job : AD_ASST UPDATE hr.emp SET name = 'james' WHERE id = 200; -- 저장, 트리거 조건에 미해당 |
| 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; / -- ORA-20000: Employee cannot earn more then $15,000 -->> 300성공 -> 400에러 -> ROLLACK : 프로그램 내에선 하나로 보아 에러 시 비정상 종료 |
# 행트리거내에서 old, new 수식자를 사용해서 데이터 변경 이전과 이후 값을 참조할 수 있다.
- BEGIN 절 안에서 old, new 수식자 앞에 콜론(:) 접두어로 붙여야 한다.
- WHEN 절에서는 old, new 수식자 앞에 콜론(:) 접두어를 붙이면 안된다.
| 작업 | OLD | NEW |
| INSERT | NULL | 새로운 입력 값 |
| UPDATE | 갱신전값 | 갱신후값 |
| DELETE | 삭제전값 | NULL |
| WHEN 절 | old. | new. |
| BEGIN 절 | :old. | :new. |
| /* 백업 테이블 생성*/ 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); |
| /* emp_source테이블의 작업 시 emp_target테이블에 백업 */ 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; / |
| INSERT INTO hr.emp_source(id,name,day,sal) VALUES (100,'oracle',default,1000); UPDATE hr.emp_source SET sal = 2000 WHERE id = 100; UPDATE hr.emp_source SET name = 'james' WHERE id = 100; DELETE FROM hr.emp_source WHERE id = 100; SELECT * FROM hr.emp_source; SELECT * FROM hr.emp_target; |
| < sys session > /* 유저 생성 */ CREATE USER green IDENTIFIED BY oracle; GRANT CREATE SESSION TO green; |
| /* 테이블 생성 audit_emp_sal */ CREATE TABLE audit_emp_sal( name varchar2(30), day timestamp, id number, old_sal number, new_sal number); /* 프로시저 생성 update_proc */ CREATE OR REPLACE PROCEDURE update_proc(p_id IN number) IS BEGIN UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = p_id; END update_proc; / /* test */ SELECT salary FROM hr.employees WHERE employee_id = 100; execute update_proc(100) SELECT salary FROM hr.employees WHERE employee_id = 100; rollback; |
| /* 유저 권한부여 */ GRANT EXECUTE ON hr.update_proc TO green; SELECT * FROM user_tab_privs; |
| /* 트리거 생성 : 접속한 유저가 INSERT 작업 시 audit_emp_sal 테이블에 정보 기입 */ CREATE OR REPLACE TRIGGER sal_audit AFTER UPDATE OF salary ON hr.employees FOR EACH ROW WHEN (new.salary != old.salary) BEGIN INSERT INTO hr.audit_emp_sal (name, day, id, old_sal, new_sal) /* user : 현재 접속한 유저 */ VALUES (user, systimestamp, :old.employee_id, :old.salary, :new.salary); END; / || 또는 CREATE OR REPLACE TRIGGER sal_audit after UPDATE OF salary ON hr.employees FOR EACH ROW BEGIN /* WHEN 대신 IF 사용 */ IF new.salary != old.salary THEN INSERT INTO hr.audit_emp_sal (name,day,id,old_sal,new_sal) VALUES (user,systimestamp,:old.employee_id,:old.salary,:new.salary); END IF; END; / |
| <green session> /* 권한 확인 */ SELECT * FROM user_tab_privs; execute hr.update_proc(100) --> green 유저가 작업 -> 트리거 작동 |
| SELECT * FROM hr.audit_emp_sal; --> hr 입장에서는 안보인다. |
| <green session> COMMIT; --> 영구저장 후 테이블에 반영되어 audit_emp_sal 테이블에서 확인 됨 |
| <green session> execute hr.update_proc(101) -- green 유저가 트랜젝션 시작 |
| UPDATE hr.employees SET salary = salary * 1.2 WHERE employee_id = 101; --> hr 입장에선 작업대기 중 |
| <green session> rollback; -->> green 유저가 트랜젝션을 종료하여 hr 작업물 진행 |
| SELECT * FROM hr.audit_emp_sal; --> hr 조회 됨. 트랜잭션 진행 중 rollback; -- 트랜젝션 종료 |
# 여러 사용자가 있을 경우 같은 테이블의 행에 대해 트랜젝션 진행 중이면 동시 작업 불가
# 작업 진행 후 트랜젝션까지 종료 가능하게 셋팅해야한다.
/* 트리거 확인 및 삭제*/
SELECT * FROM user_triggers;
DROP TRIGGER sal_audit;
/* 프로시저 확인 및 삭제 */
SELECT * FROM user_source WHERE name = 'UPDATE_PROC';
DROP PROCEDURE update_proc;
/* 오브젝트 정보 */
SELECT * FROM USER_OBJECTS;
| [문제] 사원들의 신규 데이터 또는 급여를 수정할 때 그 사원의 job_id별 최저 임금에서 최고 임금 사이에 급여값으로 입력, 수정 되어야 합니다. |
| SELECT j.job_id, j.min_salary, j.max_salary FROM hr.employees e, hr.jobs j WHERE e.job_id = j.job_id AND e.employee_id = 115; CREATE OR REPLACE TRIGGER hr.check_salary_trg BEFORE INSERT OR UPDATE ON hr.employees FOR EACH ROW -- 행단위에서 new, old 사용 가능 DECLARE min_sal number; max_sal number; BEGIN SELECT min_salary, max_salary INTO min_sal, max_sal FROM hr.jobs WHERE job_id = :new.job_id; -- new: INSERT OR UPDATE IF :new.salary NOT BETWEEN min_sal AND max_sal THEN RAISE_APPLICATION_ERROR(-20100,'Invalid salary '||trim(to_char(:new.salary,'$999999'))||'. Salaries for job '|| :new.job_id||' must be between '||trim(to_char(min_sal,'$999999'))||' and '|| trim(to_char(max_sal,'$999999'))); END IF; END; / |
| /* 호출 */ UPDATE hr.employees SET salary = 5000 WHERE employee_id = 115; -- ORA-20100: Invalid salary $6000. Salaries for job PU_CLERK must be between $2500 and $5500 INSERT INTO hr.employees(employee_id,last_name,email,hire_date,job_id,salary) VALUES (300,'oracle','aa@aa.com',sysdate,'IT_PROG',20000); -- ORA-20100: Invalid salary $20000. Salaries for job IT_PROG must be between $4000 and $10000 |
# INSERT 작업 시 NOT NULL 정보 체크
desc hr.employees
| /* 테이블 생성 hr.new_emp */ CREATE TABLE hr.new_emp AS SELECT employee_id, last_name, salary, department_id, email, job_id, hire_date FROM hr.employees; /* 테이블 생성 hr.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; SELECT * FROM hr.new_emp; SELECT * FROM hr.new_dept; |
| /* 복합 뷰 생성 emp_details */ 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; SELECT * FROM emp_details; |
# 복합뷰는 DML 작업을 수행할 수 없다.
# 복합뷰에 대해서 DML 작업을 수행할 수 있도록 하려면 TRIGGER 를 만들어서 해결할 수 있다.
| CREATE OR REPLACE TRIGGER emp_dept_trigger /* 뷰에서는 INSTEAD OF 전용. BEFORE, AFTER 사용 불가*/ INSTEAD OF INSERT OR UPDATE OR DELETE ON hr.emp_details /* 뷰에서는 무조건 행 트리거 사용 */ FOR EACH ROW BEGIN IF INSERTING THEN /* new_emp 테이블에 사원 정보 추가 */ 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); /* new_dept 테이블에 부서별 급여 업데이트 */ UPDATE hr.new_dept SET tot_dept_sal = tot_dept_sal + :new.salary WHERE department_id = :new.department_id; ELSIF DELETING THEN /* new_emp 테이블에 사원 정보 삭제 */ DELETE FROM hr.new_emp WHERE employee_id = :old.employee_id; /* new_dept 테이블에 부서별 급여 업데이트 */ UPDATE hr.new_dept SET tot_dept_sal = tot_dept_sal - :old.salary WHERE department_id = :old.department_id; ELSIF UPDATING('salary') THEN /* new_emp 테이블의 salary 업데이트 */ UPDATE hr.new_emp SET salary = :new.salary * 1.1 WHERE employee_id = :new.employee_id; /* new_dept 테이블의 부서별 급여 업데이트 */ 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 /* new_emp 테이블의 해당 사원에 대한 부서번호 업데이트 */ UPDATE hr.new_emp SET department_id = :new.department_id WHERE employee_id = :new.employee_id; /* new_dept 테이블의 변경전 부서에 대한 부서별급여 업데이트 */ UPDATE hr.new_dept SET tot_dept_sal = tot_dept_sal - :old.salary WHERE department_id = :old.department_id; /* new_dept 테이블의 변경후 부서에 대한 부서별급여 업데이트 */ UPDATE hr.new_dept SET tot_dept_sal = tot_dept_sal + :new.salary WHERE department_id = :new.department_id; END IF; END; / |
| INSERT INTO hr.emp_details(employee_id,last_name,salary,department_id,email,job_id,hire_date) VALUES (300,'oracle',1000,10,'oracle','IT_PROG',sysdate); DELETE FROM hr.emp_details WHERE employee_id = 300; UPDATE hr.emp_details SET salary = salary * 1.1 WHERE employee_id = 101; UPDATE hr.emp_details SET department_id = 20 WHERE employee_id = 101; |
- 문장트리거 : 영향이 있든 없든 무조건 실행
- 행트리거 : 영향을 받은 행에 대해 실행
FOR EACH ROW -- 필수
WHEN ( 조건 ) -- 옵션
# RAISE_APPLICATION_ERROR(-20000, 메세지)
강제 비정상 종료이기 때문에 ROLLBACK 발생
| 작업 | OLD | NEW |
| INSERT | NULL | 새로운 입력 값 |
| UPDATE | 갱신전값 | 갱신후값 |
| DELETE | 삭제전값 | NULL |
| WHEN 절 | old. | new. |
| BEGIN 절 | :old. | :new. |
# 여러 사용자가 있을 경우 같은 테이블의 행에 대해 트랜젝션 진행 중이면 동시 작업 불가
# 작업 진행 후 트랜젝션까지 종료 가능하게 셋팅해야한다.
/* 오브젝트 정보 */
SELECT * FROM USER_OBJECTS;
# INSERT 작업 시 칼럼에 대한 NOT NULL 정보 체크
DESC 소유자.테이블명
# 복합뷰는 DML 작업을 수행할 수 없다.
# 복합뷰에 대해서 DML 작업을 수행할 수 있도록 하려면 TRIGGER 를 만들어서 해결할 수 있다.
CREATE OR REPLACE TRIGGER 트리거명
INSTEAD OF -- INSTEAD OF : VIEW 전용. BEFORE, AFTER 사용 불가
INSERT OR UPDATE OR DELETE ON 테이블명
FOR EACH ROW -- VIEW 에서는 무조건 행 트리거 사용
WHEN () -- 행트리거 사용 시 옵션
DECLARE -- 옵션
BEGIN
END;
/
- USER : 작업중인 session의 작업자명
SELECT user
FROM dual;
'Oracle SQL' 카테고리의 다른 글
| 24 PL/SQL Autonomous Transaction (0) | 2023.11.08 |
|---|---|
| 22 PL/SQL PACKAGE, TRIGGER (1) | 2023.11.06 |
| 21 PL/SQL PACKAGE (1) | 2023.11.03 |
| 20 PL/SQL PROCEDURE, FUNCTION (0) | 2023.11.02 |
| 19 PL/SQL PROCEDURE (0) | 2023.11.01 |