TRIGGER를 이용하여 DML을 AUDIT하는 예제
=====================================

1. AUDIT 테이블 생성.

CREATE TABLE emp_audit (
old_empno NUMBER(4),
old_ename VARCHAR2(10),
old_job VARCHAR2(9),
old_mgr NUMBER(4),
old_hiredate DATE,
old_sal NUMBER(7,2),
old_comm NUMBER(7,2),
old_deptno NUMBER(2),
new_empno NUMBER(4),
new_ename VARCHAR2(10),
new_job VARCHAR2(9),
new_mgr NUMBER(4),
new_hiredate DATE,
new_sal NUMBER(7,2),
new_comm NUMBER(7,2),
new_deptno NUMBER(2),
changed_by VARCHAR2(8),
change_type CHAR(1),
timestamp DATE );

2. TRIGGER 생성.

CREATE OR REPLACE TRIGGER LogEmpChanges
BEFORE INSERT OR DELETE OR UPDATE ON scott.emp
FOR EACH ROW
DECLARE
v_ChangeType CHAR(1);
BEGIN
/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
IF INSERTING THEN
v_ChangeType := 'I';
ELSIF UPDATING THEN
v_ChangeType := 'U';
ELSE
v_ChangeType := 'D';
END IF;

/* Record all the changes made to scott.emp in
emp_audit. Use SYSDATE to generate the timestamp, and
USER to return the userid of the current user. */
INSERT INTO emp_audit
(change_type, changed_by, timestamp, old_empno,
old_ename, old_job, old_mgr, old_hiredate, old_sal,
old_comm, old_deptno, new_empno, new_ename, new_job,
new_mgr, new_hiredate, new_sal, new_comm, new_deptno)
VALUES
(v_ChangeType, USER, SYSDATE, :old.empno, :old.ename,
:old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm,
:old.deptno, :new.empno, :new.ename, :new.job, :new.mgr,
:new.hiredate, :new.sal, :new.comm, :new.deptno);
END LogEmpChanges;
/

3. AUDIT 결과 확인

SQL> delete from emp where empno=7934;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from emp_audit ;

OLD_EMPNO OLD_ENAME OLD_JOB OLD_MGR OLD_HIREDATE OLD_SAL
---------- ---------- --------- ---------- ------------------- ----------
OLD_COMM OLD_DEPTNO NEW_EMPNO NEW_ENAME NEW_JOB NEW_MGR
---------- ---------- ---------- ---------- --------- ----------
NEW_HIREDATE NEW_SAL NEW_COMM NEW_DEPTNO CHANGED_ C
------------------- ---------- ---------- ---------- -------- -
TIMESTAMP
-------------------
7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300
10
SCOTT D
2001/11/14 19:54:41