Seorang Database Administrator (DBA) terkadang dihadapkan pada situasi dimana terdapat permintaan informasi mengenai perubahan data yang terjadi pada sistem database yang menjadi tanggung jawabnya.
Sistem database RDBMS telah menyediakan mekanisme pencatatan perubahan data untuk proses audit trail, namun terkadang mekanisme audit trail yang ada dirasa tidak mencukupi. Kita mungkin membutuhkan informasi mengenai data lama sebelum di update atau dihapus untuk mengetahui history perubahan data atau sering disebut sebagai Value-Based Auditing (VBA). Salah satu mekanisme VBA adalah dengan menggunakan trigger pada sistem database RDBMS untuk mencatat perubahan data atau dikenal sebagai Log trigger. Tulisan berikut berisi pengenalan Log trigger pada database MySQL beserta contoh implementasinya pada database referensi RKAKL DIPA.
Log trigger
Log trigger atau History trigger merupakan sebuah mekanisme otomatis untuk mencatat perubahan data pada sebuah tabel karena proses insert, update, atau delete dengan menggunakan fungsi trigger pada database.
Perubahan data akan tercatat pada tabel log (history) yang dapat memuat informasi mengenai proses perubahan (insert, update, atau delete), nilai (isi) data sebelum dan sesudah perubahan, komputer yang digunakan untuk melakukan perubahan dan waktu terjadinya perubahan.
Secara sederhana proses Log trigger dapat diilustrasikan sebagai berikut :

User melakukan perubahan data (insert, update, delete) melalui antar muka aplikasi atau melalui akses langsung ke database (console). Proses tersebut akan mengirimkan perintah ke sistem database untuk melakukan perubahan data pada tabel. Proses perubahan data (insert, update, delete) pada tabel akan memicu trigger untuk menjalankan perintah yang akan mencatat perubahan data ke tabel history (log_table).
Proses pencatatan perubahan data ke tabel history (log_table) dapat dilakukan sebelum atau sesudah proses perubahan data terjadi pada tabel, hal ini ditentukan oleh pilihan trigger yang digunakan (after atau before).
Log_tabel sebagai tabel history dapat berada pada database yang sama dengan table (Database 1) atau pada database yang berbeda (Database 2) dalam satu server database. Penggunaan database yang terpisah lebih memberikan jaminan keamanan karena kewenangan dan hak akses terhadap tabel history dapat atur hanya dimiliki oleh administrator.
Adapun keunggulan penggunaan Log trigger adalah :
  • Sederhana dan mudah diimplementasikan;
  • Merupakan fitur standar yang tersedia hampir di semua RDBMS;
  • Bekerja secara otomatis, sekali Log trigger dibuat proses selanjutnya akan berjalan secara otomatis;
  • Tidak memerlukan perubahan struktur tabel pada tabel yang akan di-log, karena log data disimpan pada tabel yang berbeda;
  • Hanya bekerja pada proses insert, update, dan delete yang didefinisikan terlebih dahulu sehingga kebutuhan besarnya alokasi tabel history (log) dapat diperkirakan;
  • Dapat diimplementasikan pada beberapa tabel atau kolom tertentu saja, tidak harus pada seluruh tabel yang ada di database.
  • Mencatat seluruh perubahan data baik yang dilakukan melalui antar muka aplikasi maupun perubahan data yang dilakukan melalui akses langsung ke database.
Untuk dapat menggunakan Log trigger kita tidak dapat terlepas dari pemahaman mengenai trigger. Trigger merupakan kumpulan perintah yang secara otomatis akan dijalankan jika terjadi operasi tertentu pada sebuah tabel atau view. Trigger dapat menjalankan satu atau beberapa perintah SQL secara otomatis sebelum atau sesudah terjadi proses INSERT, UPDATE, dan DELETE pada sebuah tabel.
Selain dapat digunakan untuk melakukan pencatatan perubahan pada sebuah tabel, trigger juga dapat digunakan untuk melakukan update data secara otomatis dan validasi/verifikasi data sebelum data disimpan di database.
Hampir seluruh database RDBMS memiliki trigger. MySQL menambahkan dukungan trigger sejak versi 5.0.2. Walaupun trigger di MySQL memiliki hampir seluruh fitur SQL, namun ada beberapa batasan yang tidak dapat dilakukan oleh trigger di MySQL, yaitu :
  • Tidak dapat menggunakan perintah : SHOW, LOAD DATA, LOAD TABLE, BACKUP DATABASE, RESTORE, FLUSH, dan RETURN;
  • Tidak dapat menggunakan perintah commit atau rollback baik secara implisit maupun eksplisit seperti : COMMIT, ROLLBACK, START TRANSCATION. LOCK/UNLOCK TABLES, ALTER, CREATE, DROP, RENAME;
  • Tidak dapat menggunakan perintah prepare statement seperti PREPARE dan EXECUTE;
  • Tidak dapat menggunakan dynamic SQL Statement;
  • Tidak dapat menjalankan store procedure atau store function melalui trigger.
Untuk membuat sebuah trigger di MySQL perintah umum yang dapat digunakan adalah :
?
CREATE TRIGGER triggername
[BEFORE|AFTER] [INSERT|UPDATE|DELETE]
ON tablename
FOR EACH ROW statement
Keterangan :
– triggername, nama trigger dengan ketentuan sebagaimana penamaan variabel atau identifier di MySQL;
– [BEFORE|AFTER], digunakan untuk menentukan kapan proses secara otomatis akan di jalankan, sebelum atau sesudah terjadinya proses perubahan data;
– [INSERT|UPDATE|DELETE], digunakan untuk menentukan proses (event) yang dijadikan trigger (pemicu) untuk menjalankan perintah dalam statement;
– tablename, merupakan nama tabel dimana trigger berada;
– statement, merupakan sekumpulan perintah SQL atau query yang secara otomatis akan dijalankan.
Statement atau perintah dalam trigger dapat berupa satu perintah SQL atau beberapa perintah SQL, jika terdapat beberapa perintah SQL dalam trigger maka gunakan perintah BEGIN dan END untuk mengawali dan mengakhiri perintah.
Di dalam statement kita dapat mengakses data (record) sebelum atau sesudah proses dengan menggunakan perintah NEW atau OLD. NEW digunakan untuk mendapatkan nilai record yang akan diproses (insert atau update), sedangkan OLD digunakan untuk mendapatkan nilai record yang sudah diproses (update atau delete).
Trigger pada database MySQL merupakan objek yang harus didefinisikan terlebih dahulu sebagaimana store procedure dan function. Saat mendefinisikan trigger perlu dicermati penggunaan titik koma (;) pada statement SQL. MySQL secara default menganggap titik koma (;) sebagai delimiter (pembatas akhir sebuah perintah) sehingga trigger yang memiliki beberapa statement tidak akan berjalan sempurna.
Untuk mengantisipasi masalah tersebut, delimiter pada trigger perlu diganti menjadi selain tanda titik koma (;). Penulis menggunakan tanda $$ untuk menggantikan delimiter titik koma (;). Untuk mengubah delimiter sebelum mendefinisikan objek (trigger) gunakan statement ‘DELIMITER” diikuti tanda pemisah baru. Pada akhir pendefinisian objek (trigger) delimiter harus dikembalikan ke tanda titik koma (;).
?
DELIMITER $$
CREATE TRIGGER t_wenang_i AFTER INSERT ON t_wenang FOR EACH ROW
BEGIN
//SQL statement
END;
$$
DELIMITER ;
Berikut adalah contoh implementasi Log trigger untuk mencatat perubahan data pada database referensi RKAKL DIPA.

Referensi RKAKL DIPA
Database referensi RKAKL DIPA merupakan database yang berisi seluruh tabel referensi yang digunakan secara bersama antara Direktorat Jenderal Anggaran (DJA) dan Direktorat Jenderal Perbendaharaan (DJPbn) sebagai acuan referensi proses perencanaan dan pelaksanaan anggaran. Perubahan data referensi akan berpengaruh pada dokumen dan laporan yang dihasilkan sehingga konsistensi dan kebenaran data referensi harus dapat diandalkan.
Perubahan data referensi dapat dilakukan baik oleh DJA ataupun DJPbn sesuai dengan kewenangan yang telah disepakati bersama melalui sistem aplikasi RKAKL DIPA Online. Perubahan data referensi secara langsung melalui akses database masih dimungkinkan untuk mengakomodir perubahan data dalam jumlah besar dengan waktu yang terbatas.
Catatan seluruh proses perubahan data (insert, update, delete) yang terjadi pada data referensi kedalam sebuah sistem log data diperlukan sebagai audit trail dimana proses log harus dapat mencatat seluruh perubahan yang terjadi baik yang dilakukan melalui antar muka aplikasi maupun perubahan yang dilakukan secara langsung melalui akses database.
Proses pencatatan perubahan data harus dapat berjalan secara otomatis saat terjadi perubahan data referensi, Log trigger merupakan jawaban atas kebutuhan ini.
Tahapan pembuatan Log trigger yang penulis lakukan adalah sebagai berikut :
Membuat database log.
Database log merupakan tempat dimana seluruh tabel log akan berada, sebagai contoh untuk log database referensi tahun 2014 (dbref2014) penulis menggunakan nama database : dblogref2014.
Berikut perintah untuk membuat database dblogref2014 melalui console mysql :
create database dblogref2014;
Membuat tabel log.
Tabel log digunakan untuk menampung informasi mengenai jenis proses, perubahan data (data sebelum dan sesudah perubahan), tanggal proses, dan komputer yang digunakan untuk proses perubahan data. Tabel log yang dibuat adalah sejumlah tabel referensi yang ada di database referensi RKAKL DIPA.
Untuk menjamin konsistensi antara nama tabel referensi dengan nama tabel log, penulis menggunakan pola penamaan tabel log : log_[namatabel] contoh : nama tabel log untuk tabel Unit Eselon I (t_unit) adalah : log_t_unit
NamaTabel
Log_NamaTabel
Keterangan
field a
field_a
Nama field ke 1
field b
field_b
Nama field ke 2
field c
field_n
Nama field ke … (n)

field_a0
Nama field ke 1 (data awal)

field_b0
Nama field ke 2 (data awal)

field_n0
Nama field ke … (data awal)

kd_ruh
Kode R (rekam) U (ubah) H (hapus)

tgl_ruh
Tanggal proses

com_ruh
Nama/IP computer
Jumlah field pada tabel log adalah dua kali jumlah field pada tabel referensi (akhiran 0 pada setiap field tabel log menunjukkan informasi data setelah proses update) ditambah field untuk menampung kode proses , waktu proses, dan informasi komputer yang digunakan.
Tabel t_unit dengan struktur data :
?
Kddept        char(3)   Kode Departemen (Kementerian/Lembaga)
kdunit        char(2)   Kode Unit Eselon I
nmunit        char(200)Nama Unit Eselon I
jabatan1      char(200)Jabatan penanda tangan DIPA (header 1)
jabatan2      char(200)Jabatan penanda tangan DIPA (header 2)
nip           char(100)NIP/NRP
nama          char(150)Nama pejabat penanda tangan DIPA
kdupdate      char(1)   Kode update (antar muka aplikasi)
updater       char(50)  User ID update (antar muka aplikasi)
tglupdate     datetime  Tanggal update (antar muka aplikasi)
email         char(200)Alamat email Eselon I untuk notifikasi
Akan memiliki struktur data log (log_t_unit) sebagai berikut :
?
Kddept        char(3)   Kode Departemen (Kementerian/Lembaga)
kdunit        char(2)   Kode Unit Eselon I
nmunit        char(200)Nama Unit Eselon I
jabatan1      char(200)Jabatan penanda tangan DIPA (header 1)
jabatan2      char(200)Jabatan penanda tangan DIPA (header 2)
nip           char(100)NIP/NRP
nama          char(150)Nama pejabat penanda tangan DIPA
kdupdate      char(1)   Kode update (antar muka aplikasi)
updater       char(50)  User ID update (antar muka aplikasi)
tglupdate     datetime  Tanggal update (antar muka aplikasi)
email         char(200)Alamat email Eselon I untuk notifikasi
kd_ruh        char(1)   Kode update trigger
com_ruh       char(5)   IP/Nama komputer
tgl_ruh       datetime  Tanggal dan waktu proses update (trigger)
Kddept0       char(3)   Kode Departemen [data awal]
kdunit0       char(2)   Kode Unit Eselon I [data awal]
nmunit0       char(200)Nama Unit Eselon I [data awal]
jabatan10     char(200)Jabatan penanda tangan DIPA (header 1)[data awal]
jabatan20     char(200)Jabatan penanda tangan DIPA (header 2)[data awal]
nip0          char(100)NIP/NRP [data awal]
nama0         char(150)Nama pejabat penanda tangan DIPA [data awal]
kdupdate0     char(1)   Kode update (antar muka aplikasi)[data awal]
updater0      char(50)  User ID update (antar muka aplikasi)[data awal]
tglupdate0    datetime  Tanggal update (antar muka aplikasi)[data awal]
email         char(200)Alamat email Eselon I untuk notifikasi[data awal]
DDL (Data Definition Language) untuk membuat tabel t_unit :
?
CREATE TABLE t_unit (
kddept char(3) NOT NULL,
kdunit char(2) NOT NULL,
nmunit char(200) NOT NULL,
jabatan1 char(200) NOT NULL,
jabatan2 char(200) NOT NULL,
nip char(100) NOT NULL,
nama char(150) NOT NULL,
kdupdate char(1) NOT NULL,
updater char(5) NOT NULL,
tglupdate datetime NOT NULL,
email char(200) DEFAULT NULL,
PRIMARY KEY (kddept,kdunit)
) ENGINE=MyISAM
DDL untuk membuat tabel log_t_unit :
?
CREATE TABLE log_t_unit (
kd_ruh char(1) NOT NULL,
com_ruh char(50) NOT NULL,
tgl_ruh datetime NOT NULL,
kddept char(3) NOT NULL,
kdunit char(2) NOT NULL,
nmunit char(200) NOT NULL,
jabatan1 char(200) NOT NULL,
jabatan2 char(200) NOT NULL,
nip char(100) NOT NULL,
nama char(150) NOT NULL,
kdupdate char(1) NOT NULL,
updater char(5) NOT NULL,
tglupdate datetime NOT NULL,
email char(200) DEFAULT NULL,
kddept0 char(3) NOT NULL,
kdunit0 char(2) NOT NULL,
nmunit0 char(200) NOT NULL,
jabatan10 char(200) NOT NULL,
jabatan20 char(200) NOT NULL,
nip0 char(100) NOT NULL,
nama0 char(150) NOT NULL,
kdupdate0 char(1) NOT NULL,
updater0 char(5) NOT NULL,
tglupdate0 datetime NOT NULL,
email0 char(200) DEFAULT NULL,
PRIMARY KEY (kddept,kdunit)
) ENGINE=MyISAM
Untuk selanjutnya tulisan ini akan menggunakan tabel t_unit dan log_t_unit sebagai contoh implementasi Log trigger.
Membuat trigger
Karena database MySQL belum mendukung proses insert, update, dan delete dalam sebuah trigger maka untuk setiap proses (insert, update, dan delete) perlu dibuatkan masing-masing trigger sebagai berikut :
  1. Trigger insert
    Nama trigger insert : [NamaTabel]_i, contoh : t_unit_i
    Perintah untuk membuat trigger insert pada tabel t_unit :
?
</pre>
DELIMITER $$
DROP TRIGGER dbref2014.t_unit_i $$
CREATE TRIGGER dbref2014.t_unit_i AFTER INSERT ON dbref2014.t_unit
FOR EACH ROW BEGIN
INSERT INTO dbLogRef2014.log_t_unit(kd_ruh,com_ruh,tgl_ruh,kddept,kdunit,
nmunit,jabatan1,jabatan2,nip,nama,kdupdate,updater,tglupdate,email) values
('i', user(),now(),new.kddept,new.kdunit,new.nmunit,new.jabatan1,
new.jabatan2,new.nip,new.nama,new.kdupdate,new.updater,new.tglupdate,
new.email);
END;
$$
DELIMITER ;
<pre>
  1. Trigger update
    Nama trigger update : [NamaTabel]_u, contoh : t_unit_u
    Perintah untuk membuat trigger update pada tabel t_unit :
?
</pre>
DELIMITER $$
DROP TRIGGER dbref2014.t_unit_u $$
CREATE TRIGGER dbref2014.t_unit_u AFTER UPDATE ON dbref2014.t_unit
FOR EACH ROW BEGIN
   INSERT INTO dbLogRef2014.log_t_unit(kd_ruh, com_ruh, tgl_ruh,kddept,
   kdunit,nmunit,jabatan1,jabatan2,nip,nama,kdupdate,updater,tglupdate,
   email,kddept0,kdunit0,nmunit0,jabatan10,jabatan20,nip0,nama0,kdupdate0,
   updater0,tglupdate0,email0) values ('u', user(),now(),old.kddept,
   old.kdunit,old.nmunit,old.jabatan1,old.jabatan2,old.nip,old.nama,
   old.kdupdate,old.updater,old.tglupdate,old.email,new.kddept,new.kdunit,
   new.nmunit,new.jabatan1,new.jabatan2,new.nip,new.nama,new.kdupdate,
   new.updater,new.tglupdate,new.email);
END;
$$
DELIMITER ;
<pre>
  1. Trigger delete
    Pola nama trigger delete : [NamaTabel]_d, contoh : t_unit_d)
    Perintah untuk membuat trigger delete untuk tabel t_unit :
?
</pre>
DELIMITER $$
DROP TRIGGER dbref2014.t_unit_d $$
CREATE TRIGGER dbref2014.t_unit_d AFTER DELETE ON dbref2014.t_unit
FOR EACH ROW BEGIN
   INSERT INTO dbLogRef2014.log_t_unit(kd_ruh, com_ruh,tgl_ruh,kddept,
   kdunit,nmunit,jabatan1,jabatan2,nip,nama,kdupdate,updater,tglupdate,
   email) values ('d', user(),now(),old.kddept,old.kdunit,old.nmunit,
   old.jabatan1,old.jabatan2,old.nip,old.nama,old.kdupdate,old.updater,
  old.tglupdate,old.email);
END;
$$
DELIMITER ;
<pre>
Perintah DELIMITER $$ berfungsi untuk menggantikan titik koma (;) sebagai delimiter default MySQL menjadi $$.
Perintah DROP TRIGGER dbref2014.t_unit_i berfungsi untuk menghapus trigger t_unit_i pada database dbref2014 jika trigger dengan nama t_unit_i sudah ada pada tabel t_unit di database dbref2014. Delimiter $$ pada akhir baris menandakan bahwa baris perintah tersebut merupakan sebuah statement yang akan di jalankan.
Perintah CREATE TRIGGER dbref2014.t_unit_i berfungsi untuk membuat trigger dengan nama t_unit_i pada tabel t_unit di database dbref2014.
Perintah AFTER … merupakan action time yang menunjukkan kapan perintah akan dijalankan, pada trigger t_unit_i perintah akan dijalankan setelah proses delete pada tabel t_unit selesai dijalankan.
Perintah FOR EACH ROW merupakan trigger body yang berisi perintah-perintah yang akan dijalankan setiap kali trigger diaktifkan.
Perintah BEGIN menunjukkan awal statement SQL, dapat berupa satu atau beberapa baris perintah SQL.
Perintah INSERT INTO … merupakan statement SQL yang akan dijalankan oleh trigger. Dalam statement SQL nilai kd_ruh diisi sesuai dengan proses yang terjadi (r untuk rekam/insert, u untuk ubah/update, dan h untuk hapus/delete), user() digunakan untuk mendapatkan nilai IP atau nama komputer yang digunakan untuk melakukan proses, now() digunakan untuk mendapatkan nilai tanggal dan waktu proses.
new.[namafield] akan berisi nilai yang akan menggantikan isi nilai filed dan old.[namafield] akan berisi nilai yang akan digantikan dalam sebuah filed. new.[namafield] digunakan pada trigger insert dan update, untuk mendapatkan nilai saat proses perekaman dan perubahan data. old.[namafield] digunakan pada trigger delete untuk mendapatkan nilai data yang dihapus.
Pendefinisian nama database pada perintah INSERT INTO dbLogRef2014.log_t_unit diperlukan karena database dimana tabel log_t_unit berada terletak pada database (dblogref2014) yang berbeda dengan tabel t_unit (dbref2014).
Perintah END; menunjukkan akhir statement SQL.
Perintah $$ digunakan untuk memberikan perintah kepada MySQL untuk menjalankan perintah atau perintah-perintah yang didefinisikan pada bagian BEGIN hingga END. Perintah DELIMITER ; berfungsi mengembalikan nilai default delimiter menjadi titik koma (;).
Untuk setiap tabel referensi akan mempunyai tiga trigger.

Menguji Log trigger
Pengujian Log trigger dilakukan dengan memberikan perintah insert, update, dan delete pada tabel t_unit dan memonitor hasilnya pada tabel log_t_unit.
  • Insert
    Perintah berikut akan menambahkan data unit 03 pada KL 001 :
?
<pre>
INSERT INTO t_unit(kddept, kdunit, nmunit, jabatan1, jabatan2, nip, nama, kdupdate, updater, tglupdate, email VALUES ('001', '03', 'MPR (test)', 'WAKIL SEKRETARIS JENDERAL', '', 'NIP 10560618198', 'Dra. SELFI', '', '', '', 'anggaran.mpr@gmail.com');
</pre>
  • Update
    Perintah berikut akan mengubah nomenklatur unit 03 pada KL 001 dari MPR (test) menjadi MPR :
?
<pre>
update t_unit set nmunit ='MPR' where kddept ='001' and kdunit = '003';
</pre>
  • Delete
    Perintah berikut akan menghapus data unit 03 pada KL 001 :
?
<pre>
delete from t_unit where kddept ='001' and kdunit = '03';
</pre>
Hasil Log trigger dapat dilihat pada tabel log_t_unit sebagai berikut :

?
SELECT kd_ruh, com_ruh, tgl_ruh, kddept, kdunit, nmunit, kddept0, kdunit0, nmunit0 FROM log_t_unit;
Pembatasan query dilakukan untuk menyederhanakan hasil.
Dari hasil log tabel akan diperoleh informasi :
    • Proses insert (kd_ruh : i) dilakukan oleh user di2k melalui komputer @_ (IP), pada tanggal 02 Juni 2014 pukul 07:27:58 dengan data : kddept 001, kdunit 03, nmunit MPR (test).
    • Proses update (kd_ruh : u) dilakukan pada tanggal 02 Juni 2014 pukul 07:30:00, data sebelum perubahan terdapat pada kolom kddept, kdunit, dan nmunit dan hasil perubahan terdapat pada kolom kddept0, kdunit0, dan nmunit0
    • Proses hapus (kd_ruh : d) dilakukan pada tanggal 02 Juni 2014 pukul 07:33:42
Sampai dengan tulisan ini dibuat tabel referensi pada database referensi RKAKL DIPA tahun 2014 berjumlah 106 tabel.
Untuk menghasilkan tabel log dan trigger dengan nama dan struktur sebagaimana telah diuraikan diatas pada seluruh tabel referensi penulis menggunakan Microsoft Visual FoxPro dengan script sebagaimana terlampir.
Hasil program tersebut adalah membuat tabel log untuk seluruh tabel referensi dan file trigger.log yang terletak di folder c:\work\ berisi perintah SQL untuk membuat trigger di seluruh tabel referensi. Jika Anda menggunakan SQLyog pilih menu File – Open untuk membuka file trigger.log di query console selanjutnya click Execute All Queries untuk menjalankan perintah pembuatan trigger.


Capeee juga ya,,,,, akhirnya kelar juga nih tutoriallll..... semoga bermanfaat ya teman-teman......