thanks pak wadi 2008/8/12 Wadi Achmed <[EMAIL PROTECTED]>
> Kalau tujuan auditnya setiap ada data baru ataupun perubahan, jangan > pakai :old tapi pakai :new untuk insert dan update, sementara untuk > delete gunakan :old. > > Berikut contoh scriptnya > > ================== > CREATE TABLE COBA > ( > EMPNO NUMBER(4), > NAMA VARCHAR2(20), > JABATAN VARCHAR2(10), > GAJI NUMBER > ); > > ====================== > CREATE TABLE COBA_audit > ( > Tanggal_edit date, > EMPNO NUMBER(4), > NAMA VARCHAR2(20), > JABATAN VARCHAR2(10), > GAJI NUMBER, > OPERASI VARCHAR2(6) > ); > > ======================================= > BEFORE INSERT OR UPDATE OR DELETE ON COBA FOR EACH ROW > DECLARE > BEGIN > > if inserting then > insert into COBA_audit values ( > sysdate, > :new.EMPNO, > :new.NAMA, > :new.JABATAN, > :new.GAJI, > 'INSERT' > ); > > elsif updating then > insert into COBA_audit values ( > sysdate, > :new.EMPNO, > :new.NAMA, > :new.JABATAN, > :new.GAJI, > 'UPDATE' > ); > > elsif deleting then > insert into COBA_audit values ( > sysdate, > :old.EMPNO, > :old.NAMA, > :old.JABATAN, > :old.GAJI, > 'DELETE' > ); > > end if; > END; > > Di bawah ini DML yang saya coba > ============================================== > insert into COBA values (1, 'Andes', 'DBA', 400000); > commit; > ============================================== > update COBA set gaji=1000000 where empno=1; > commit; > ============================================== > delete COBA where empno = 1; > commit; > > Selanjutnya hasil query dari > ============================================== > select tanggal_edit, empno, nama, jabatan, gaji, operasi from coba_audit; > TANGGAL_EDIT EMPNO NAMA JABATAN GAJI OPERASI > 8/12/2008 5:14:13 PM 1 Andes DBA 400000 INSERT > 8/12/2008 5:14:13 PM 1 Andes DBA 1000000 UPDATE > 8/12/2008 5:14:13 PM 1 Andes DBA 1000000 DELETE > > Cheers, > Wadi Achmed > > > Andes Febrian wrote: > > Dear gurus, > > > > untuk audit proses insert knp ya data yg di tampilkan pada table audit > hanya > > tanggal. seperti contoh di bawah ini : > > ================== > > CREATE TABLE COBA > > ( > > EMPNO NUMBER(4), > > NAMA VARCHAR2(20), > > JABATAN VARCHAR2(10), > > GAJI NUMBER > > ); > > > > ====================== > > CREATE TABLE COBA_audit > > ( > > Tanggal_edit date, > > EMPNO NUMBER(4), > > NAMA VARCHAR2(20), > > JABATAN VARCHAR2(10), > > GAJI NUMBER > > ); > > > > ======================================= > > CREATE OR REPLACE TRIGGER trg_COBA_audit > > BEFORE INSERT OR DELETE OR UPDATE ON COBA > > FOR EACH ROW > > DECLARE > > BEGIN > > insert into COBA_audit values ( > > sysdate, > > :old.EMPNO, > > :old.NAMA, > > :old.JABATAN, > > :old.GAJI > > ); > > END; > > / > > > > =================================== > > insert into COBA2 values (1, 'Andes', 'DBA', 400000) > > commit; > > > > ***setelah insert, saya select * from coba_audit hasilnya adalah : > > tanggal_edit empno ename job sal 08/12/2008 16:07:33 > > > > > > > > > > =================================== > > update COBA2 set sal=1000000 where empno=1 > > commit; > > > > ****kemudian bila saya update barulah tampil, sebagai berikut : > > tanggal_edit empno ename job sal 08/12/2008 16:16:44 3 Andes DBA > > 1.000.000,00 08/12/2008 16:14:30 > > > > > > > > > > Mohon pencerahannya, terima kasih. > > > > [Non-text portions of this message have been removed] > > > -- Cheers, ^_^ Andes ^_^ [Non-text portions of this message have been removed]
