084 - [Oracle PL/SQL] Trigger - Create Audit table(다중 조건)

특정 테이블이 변경되었을 경우, 어떤 변화들이 발생했는지 자동으로 저장하는 audit 관련 트리거를 만들어 보자.

 

 

 

• 준비사항

-- 준비작업.
drop table emp_copy;
/
create table emp_copy
as select * from employees;
/
select * from emp_copy;
/
drop table EMP_SAL_AUDIT;
/


-- 테스트용 테이블 생성.
CREATE TABLE EMP_SAL_AUDIT
(	EMP_ID NUMBER, 
	OPERATION VARCHAR2(15), 
	OLD_SAL NUMBER, 
	NEW_SAL NUMBER, 
	OP_DATE DATE, 
	BY_USER VARCHAR2(10)
);

 

 

 

• 트리거 생성

-- now we need to create trigger for audit
-- 인서트에 대한 롤백이나 커밋을 처리하지 않는다.
-- 이부분은 emp_copy에 대한 롤백이나 커밋에 따라 자동으로 처리됨.

create or replace trigger emp_copy_sal_audit
after insert or update of salary or delete
on emp_copy
for each row
begin
    if inserting then
        insert into EMP_SAL_AUDIT(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
        values (:new.employee_id,'Inserting',null,:new.salary,sysdate,user);
    end if;
    
    if updating then
        insert into EMP_SAL_AUDIT(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
        values (:old.employee_id,'updating',:old.salary,:new.salary,sysdate,user);
    end if;
    
    if deleting then
        insert into EMP_SAL_AUDIT(EMP_ID,OPERATION,OLD_SAL,NEW_SAL,OP_DATE,BY_USER)
        values (:old.employee_id,'deleting',:old.salary,null,sysdate,user);
    end if;

end;

 

 

 

• 데이터 삽입

--1 testing the insert operation

-- 테스트용 데이터 삽입
insert into emp_copy (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,salary,hire_date,job_id)
values (1,'ahmed','naser','ahmed.n@hotmail.com',900,sysdate,'AD_PRES');


-- 입력된 데이터 확인.
select EMPLOYEE_ID, LAST_NAME, HIRE_DATE, SALARY from emp_copy
where EMPLOYEE_ID=1;
-------------------------
EMPLOYEE_ID LAST_NAME                 HIRE_DATE     SALARY
----------- ------------------------- --------- ----------
          1 naser                     30-APR-24        900


-- 트리거 동작 확인.
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER 
from EMP_SAL_AUDIT
order by OP_DATE;
---------------------------
    EMP_ID OPERATION          OLD_SAL    NEW_SAL OP_DATE             BY_USER   
---------- --------------- ---------- ---------- ------------------- ----------
         1 Inserting                         900 30-04-2024 14:20:17 HR

 

 

 

• 데이터 업데이트

--2 testing the update operation
-- 테스트용 데이터 업데이트.
update emp_copy
set salary=salary +10
where department_id=20;


-- 트리거 동작 확인.
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER 
from EMP_SAL_AUDIT
order by OP_DATE;
--------------------------
    EMP_ID OPERATION          OLD_SAL    NEW_SAL OP_DATE             BY_USER   
---------- --------------- ---------- ---------- ------------------- ----------
         1 Inserting                         900 30-04-2024 14:20:17 HR        
       202 updating              6000       6010 30-04-2024 14:20:43 HR        
       201 updating             13000      13010 30-04-2024 14:20:43 HR

 

 

 

• 데이터 삭제

--3 Testing the delete
-- 테스트용 테이터 삭제.
delete from emp_copy
where EMPLOYEE_ID=1;


-- 트리거 동작 확인.
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER 
from EMP_SAL_AUDIT
order by OP_DATE;
--------------------------
    EMP_ID OPERATION          OLD_SAL    NEW_SAL OP_DATE             BY_USER   
---------- --------------- ---------- ---------- ------------------- ----------
         1 Inserting                         900 30-04-2024 14:20:17 HR        
       201 updating             13000      13010 30-04-2024 14:20:43 HR        
       202 updating              6000       6010 30-04-2024 14:20:43 HR        
         1 deleting               900            30-04-2024 14:21:16 HR

 

 

 

• 롤백과 트리거 테이블

rollback;
--------------------------
Rollback complete.


-- 트리거 동작 확인. rollback을 했으므로 트리거 내용도 자동으로 롤백이 된다.
select EMP_ID,OPERATION,OLD_SAL,NEW_SAL,
to_char(OP_DATE,'dd-mm-yyyy hh24:mi:ss')OP_DATE ,BY_USER 
from EMP_SAL_AUDIT
order by OP_DATE;
--------------------------
no rows selected