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, :old.EMPNO, :old.ENAME, :old.JOB, :old.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)
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 ?
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
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
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).
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
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.
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;
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
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