Audit Update Table dengan Trigger

Oracle menyediakan tool untuk mengaudit suatu tabel dengan AUDIT_TRAIL, saya pernah membahasnya di “Audit operasi di suatu table”. Dari AUDIT_TRAIL kita bisa mengetahui update table ini dilakukan kapan, oleh user siapa, dari mesin mana, dan lain-lain.

Kadang AUDIT_TRAIL tidak cukup. Kita mungkin ingin menyimpan data lama sebelum di-update sehingga bisa mengetahui history perubahan data. Jenis audit yang seperti ini disebut sebagai Value-Based Auditing (VBA). Nah, untuk ini kita bisa menggunakan TRIGGER.

Misalkan kita punya tabel EMP2

CREATE TABLE EMP2
(
EMPNO     NUMBER(4),
ENAME     VARCHAR2(20),
JOB       VARCHAR2(10),
SAL       NUMBER
);

insert into EMP2 values (1, 'Rohmad', 'DBA', 400000);
commit;

Kita akan membuat TRIGGER yang mencatat waktu dan nilai lama sebelum di-update. Langkah pertama, buat tabel yang menampung hasil audit tersebut, misalkan kita beri nama EMP2_AUDIT.

CREATE TABLE EMP2_audit
(
Tanggal_edit date,
EMPNO     NUMBER(4),
ENAME     VARCHAR2(20),
JOB       VARCHAR2(10),
SAL       NUMBER
);

Selanjutnya buat trigger yang mencatat nilai sebelum di-update

CREATE OR REPLACE TRIGGER trg_EMP2_update
BEFORE UPDATE ON EMP2
FOR EACH ROW
DECLARE
BEGIN
insert into EMP2_audit values (
sysdate,
 :o ld.EMPNO,
 :o ld.ENAME,
 :o ld.JOB,
 :o ld.SAL
);
END;
/

Sekarang, mari kita coba update tabel EMP2 itu

update EMP2 set sal=1000000 where empno=1;
commit;

OK. Tabel sudah di-update. Sekarang kita lihat hasil auditnya:

SQL> select * from EMP2_AUDIT;

TANGGAL_E      EMPNO ENAME          JOB               SAL
--------- ---------- -------------- ---------- ----------
06-AUG-08          1 Rohmad         DBA            400000

Referensi
Oracle® Database Application Developer’s Guide – Fundamentals – 10g Release 2 (10.2)

Silahkan baca yang ini juga:

This entry was posted in Administration and tagged , , , . Bookmark the permalink.

9 Responses to Audit Update Table dengan Trigger

  1. andes says:

    alo pak rohmad,

    terima kasih atas artikelnya, membantu sangat loh :) tp saya punya pertanyaan, bisa ga trigger ini digunakan untuk mengaudit proses insert dan delete ?

  2. rohmadne says:

    Hallo Mas Andes.
    Bisa sekali. Gunakan

    CREATE OR REPLACE TRIGGER trg_EMP2_update
    BEFORE DELETE ON EMP2

    atau

    CREATE OR REPLACE TRIGGER trg_EMP2_update
    BEFORE INSERT ON EMP2

    atau

    CREATE OR REPLACE TRIGGER trg_EMP2_update
    BEFORE DELETE OR INSERT OR UPDATE ON EMP2

  3. andes says:

    satu hal lagi niy pak, (hihihi.. nanya mulu ah) klo saya update data di table sdm_coba.psl_h dari user lain, misal sdm_test, ga bisa ya pak ? tp dari user sys bisa, apa karena ga ada privileges, tp sudah saya set grant dba to sdm_test

    jadi gini, saya mau coba liat di field update_by keluar ga nama sdm_test bila saya update data di sdm_coba.psl_h. tp ketika saya coba jalankan command update malah keluar eror :

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at “SDM_COBA.TRG_PSLH_AUDIT”, line 5
    ORA-04088: error during execution of trigger ‘SDM_COBA.TRG_PSLH_AUDIT’

    apa karena table ini sudah di audit oleh triger yg saya buat jadi ga bisa di update oleh user lain selain sys ?

    thanks

  4. rohmadne says:

    Error ini sebagian besar karena BUG. Anda punya akses ke Metalink? Kalau punya, silahkan search doc metalink, kalau perlu create SR (service Request). Kalau tidak punya, ya mesti menerima semua keterbatasan tanpa akses support Oracle :)

    Tabel yang di-update ada yang memakai type data varchar2? Berapa argumennya? Misalnya varchar2 (1000)? Kalau mau coba-coba, naikkan dua kali lipat, misalnya jadi varchar2(2000).

  5. andes says:

    pak rohmad,

    ternyata dari user lain bisa, tp entah knp dari sdm_test ga bisa, mgkn karena privileges atau role nya kali ya. tp klo saya liat user ini di grant dba.

    nanya lg, klo mau menampilkan sqltext pada table audit tersebut, gmn ya pak ?

    thanks

  6. andes says:

    pak rohmad, saya mau tanya lg :)

    saya buat trigger seperti ini :
    CREATE OR REPLACE TRIGGER trg_debitur_audit
    BEFORE INSERT OR UPDATE OR DELETE ON DEBITUR
    FOR EACH ROW
    DECLARE
    CUR_USER VARCHAR2(15);
    CUR_DATE TIMESTAMP;
    BEGIN
    SELECT USER,SYSDATE INTO CUR_USER,CUR_DATE FROM DUAL;
    INSERT INTO DEBITUR_AUDIT
    VALUES (:OLD.DEB_KODE, :OLD.DEB_CAB_KODE, :OLD.CREATED_BY, :OLD.CREATED_ON, CUR_USER, CUR_DATE);
    END;
    /

    kemudian saya melakukan insert pada table debitur, tp di table debitur_audit yg keluar cuma cur_user dan cur_date aja. seperti deb_kode, deb_cab_kode, created_by dan created_on data tidak tampil.

    tp kemudian saya melakukan update dan delete pada data yg saya baru insert, barulah semua tampil pada field2 di table debitur_audit.

    yg saya tanyakan, knp ya seperti itu ? apa untuk insert, tidak semua field2 tercatat pada table ? knp harus menunggu di update atau delete pada table bersangkutan ?

    terima kasih.

  7. rohmadne says:

    Trigger anda menyimpan OLD value ke tabel DEBITUR_AUDIT. Untuk insert, ya jelas saja tidak punya OLD value; jadi gunakan NEW value untuk trigger insert.

    Sebaiknya pisahkan trigger INSERT sendiri, karena menggunakan NEW value. Sementara trigger UPDATE dan DELETE bisa disatukan, karena menggunakan OLD value.

    Bisa juga anda satukan, namun perlu ditambahkan kondisi kalau INSERT memakai NEW value dan kalau UPDATE&DELETE memakai OLD value. Contoh:

    IF INSERTING THEN
    INSERT INTO DEBITUR_AUDIT
    VALUES (:NEW.DEB_KODE, :NEW.DEB_CAB_KODE, :NEW.CREATED_BY, :NEW.CREATED_ON, CUR_USER, CUR_DATE);

    ELSE
    INSERT INTO DEBITUR_AUDIT
    VALUES (:OLD.DEB_KODE, :OLD.DEB_CAB_KODE, :OLD.CREATED_BY, :OLD.CREATED_ON, CUR_USER, CUR_DATE);

    END IF;

  8. vian says:

    pak…saya mau audit database,tp yg mau saya tampilkan semua querynya,termasuk kl user melakukan proses DML bagaimana cara menampilkan log.nya dlm oracle 9i?bisa gak ya?terimakasih banyak pak

  9. beny says:

    Dh
    Pak saya mau tanya untuk melihat jumlah row transaksi (insert, Update,delete, add) pada satu database per table, per hari, bagaimana caranya? saya menggunakan oracle 9i.
    sebelumnya terimakasih banyak

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>