Vytvoření auditní tabulky
Struktura tabulky odpovídá struktuře původní tabulky, kterou hodláme auditovat. V našem případě se jedná o tabulku EMP uživatele SCOTT.
SQL> create table emp_audit(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
AFTER_SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
user_name VARCHAR2(30),
action VARCHAR2(30),
upd_date DATE);
Table created.
Vytvoření triggeru
Trigger je jednoduchý a já jsem použil syntaxi od Ron Ben Natana s jeho svolením.
SQL> CREATE OR REPLACE TRIGGER audit_emp
2 AFTER INSERT OR DELETE OR UPDATE
3 ON SCOTT.EMP
4 FOR EACH ROW
5 BEGIN
6 IF INSERTING THEN
7 INSERT INTO emp_audit VALUES (
:new.empno,
8 9 :new.ename,
10 :new.job,
11 :new.mgr,
12 :new.hiredate,
NULL,
13 14 :new.sal,
:new.comm,
15 16 :new.deptno,
17 User,
18 ‘INSERT’,
19 Sysdate
);
ELSIF DELETING THEN
INSERT INTO emp_audit VALUES (
20 21 22 23 :old.empno,
:old.ename,
24 25 :old.job,
26 :old.mgr,
27 :old.hiredate,
28 NULL,
29 :old.sal,
30 :old.comm,
:old.deptno,
31 32 User,
‘DELETE’,
33 34 Sysdate
35 );
36 ELSIF UPDATING THEN
37 INSERT INTO emp_audit VALUES (
38 :new.empno,
39 :new.ename,
40 :new.job,
41 :new.mgr,
42 :new.hiredate,
43 :old.sal,
44 :new.sal,
45 :new.comm,
46 :new.deptno,
47 User,
48 ‘UPDATE’,
49 Sysdate
50 );
51 END IF;
52 END;
53 /
Trigger created.
Hodnoty v původní tabulce
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO OP
———- ———
7369 SMITH CLERK 7902 17-DEC-80 800
20 909418307
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 569115810
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 092782000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO OP
———- ———
7566 JONES MANAGER 7839 02-APR-81 2975
20 928344601
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 149312204
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 575318109
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO OP
———- ———
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 860486202
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 915932305
7839 KING PRESIDENT 17-NOV-81 5000
10 014139303
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO OP
———- ———
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 627234008
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO OP
———- ———
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
Provedení testovacích DML operací
SQL> delete from emp where EMPNO=7934;
1 row deleted.
SQL> update emp set SAL=SAL+2000 where SAL<960;
2 rows updated.
SQL> connect system
Enter password:
Connected.
SQL> update scott.emp set sal=3333 where EMPNO=7654;
1 row updated.
Výpis auditní tabulky
column EMPNO format 9999;
column ENAME format a10;
column JOB format a10;
column SAL format 9999;
column AFTER_SAL format 9999;
column USER_NAME format a10;
column ACTION format a10;
column UPD_DATE format a10;
select EMPNO,ENAME,JOB,SAL,AFTER_SAL,USER_NAME,ACTION,UPD_DATE from emp_audit;
EMPNO ENAME JOB SAL AFTER_SAL USER_NAME ACTION UPD_DATE
—– ———- ———- —– ——— ———- ———- ———-
7934 MILLER CLERK 1300 SCOTT DELETE 31-JAN-12
7369 SMITH CLERK 800 2800 SCOTT UPDATE 31-JAN-12
7900 JAMES CLERK 950 2950 SCOTT UPDATE 31-JAN-12
7654 MARTIN SALESMAN 1250 3333 SYSTEM UPDATE 31-JAN-12