Mengenal Trigger di MySQL

Pernahkan menemukan kasus saat men-develop aplikasi, dimana perlu melakukan update terhadap suatu field, berdasarkan isi dari field lain? Contoh: men-update field status menjadi disabled, apabila tanggal input lebih kecil dari tanggal hari ini. Biasanya, yang kita lakukan adalah dengan melakukan pengecekan di script (baik itu PHP, Java, atau lainnya) sebelum menyimpan data ke table.

Definisi Trigger

Di MySQL, ada suatu mekanisme bernama TRIGGER yang bisa digunakan sebagai alternatif untuk kasus di atas. Trigger akan menjalankan suatu perintah SQL yang bisa terjadi pada saat sebelum atau sesudah kita melakukan perintah INSERT, UPDATE, dan DELETE. Secara sederhana, syntax trigger adalah sebagai berikut:

CREATE TRIGGER nama_trigger [BEFORE/AFTER] [INSERT/UPDATE/DELETE]
ON nama_table
FOR EACH ROW
BEGIN

   //defisi trigger

END;

Di dalam blok BEGIN...END; kita bisa memasukkan sebanyak mungkin perintah SQL dengan memisahkan masing-masing perintah menggunakan tanpa titik-koma ;.

Persiapan Table Untuk Latihan

Sebelum kita berkenalan lebih jauh dengan Trigger, mari kita buat terlebih dahulu, struktur table yang dibutuhkan.

CREATE TABLE `table_master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(60) NOT NULL,
  `rating` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE `table_master_backup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(60) NOT NULL,
  `rating` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE `table_log` (
  `tanggal` date NOT NULL,
  `master_id` int(11) NOT NULL,
  `rating` int(11) DEFAULT NULL,
  UNIQUE KEY `tanggal` (`tanggal`,`master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Penjelasan penggunaan table di atas adalah sebagai berikut:

• table_master adalah master data user
• table_master_backup adalah backup dari table master data untuk user2 yang sudah dianggap tidak aktif lagi
• table_log adalah catatan kegiatan dari user.

Strukturnya tidak masuk akal? cuekin saja, soalnya saya juga bingung ingin membuat contoh kasus yang seperti apa :D.

Membuat Trigger Sederhana

Untuk contoh kasus yang akan kita lakukan adalah, setiap kali melakukan proses insert table master, hitung rating berdasarkan panjang nama. Untuk sederhananya, kita buat saja dengan formula:

rating = panjang_nama * 2

Maka, syntax trigger yang akan kita buat adalah sebagai berikut:

DELIMITER //
DROP TRIGGER IF EXISTS trigger_insert_master;
CREATE TRIGGER trigger_insert_master BEFORE INSERT ON table_master
FOR EACH ROW
BEGIN

   SET NEW.rating = LENGTH(NEW.nama) * 2;

END;//
DELIMITER ;

Setelah Trigger berhasil dibuat, sekarang kita test dengan melakukan operasi INSERT:

INSERT INTO table_master(nama) VALUES('Jagung Agung');

Perhatikan bahwa kita hanya mengisikan nama pada operasi INSERT. Coba kita lihat apa yang terjadi dengan data yang kita simpan:

mysql> SELECT * FROM table_master;
+----+--------------+--------+
| id | nama         | rating |
+----+--------------+--------+
|  1 | Jagung Agung |     24 |
+----+--------------+--------+
1 row in set (0.04 sec)

Bisa dilihat bahwa field rating juga ikut terisi.
Sebelum melangkah lebih jauh, mari kita tambahkan beberapa data terlebih dahulu:

INSERT INTO table_master(nama) VALUES('Jagung Agung');
INSERT INTO table_master(nama) VALUES('Badi Bangor');
INSERT INTO table_master(nama) VALUES('Mad Dog');

INSERT INTO table_master_backup(id, nama, rating) VALUES(5, 'Success Kids', 24);
INSERT INTO table_master_backup(id, nama, rating) VALUES(6, 'Interesting Man', 30);
INSERT INTO table_master_backup(id, nama, rating) VALUES(7, 'Yao Ming', 16);

Ok, data sudah siap. Sekarang, yang akan kita lakukan adalah saat melakukan INSERT data log, cari nilai rating user dari table master atau master_backup dan isikan nilainya sebagai nilai rating saat ini. Bagaimana syntax-nya? Silahkan dipelajari:

DELIMITER //
DROP TRIGGER IF EXISTS trigger_insert_log;
CREATE TRIGGER trigger_insert_log BEFORE INSERT ON table_log
FOR EACH ROW
BEGIN

   SET NEW.rating = (SELECT rating FROM table_master WHERE id = NEW.master_id);

   IF NEW.rating IS NULL THEN
       SET NEW.rating = (SELECT rating FROM table_master_backup WHERE id = NEW.master_id);
   END IF;

END;//
DELIMITER ;

Sekarang kita test dengan query berikut:

INSERT INTO table_log(tanggal, master_id) VALUES('2012-11-25', 3);
INSERT INTO table_log(tanggal, master_id) VALUES('2012-11-25', 7);

Bagaimana hasilnya?

mysql> SELECT * FROM table_log;
+------------+-----------+--------+
| tanggal    | master_id | rating |
+------------+-----------+--------+
| 2012-11-25 |         3 |     22 |
| 2012-11-25 |         7 |     16 |
+------------+-----------+--------+
2 rows in set (0.00 sec)

Wow, rating langsung terisi!

Menampilkan Pesan Pada Trigger

Trik terakhir yang akan saya bagi adalah, bagaimana menampilkan pesan apabila terjadi suatu kesalahan. Yaitu, dengan membuat variable dummy dan melakukan SELECT yang berisi pesan kesalahan ke variable dummy tersebut. Contohnya:

DELIMITER //
DROP TRIGGER IF EXISTS trigger_insert_log;
CREATE TRIGGER trigger_insert_log BEFORE INSERT ON table_log
FOR EACH ROW
BEGIN
   DECLARE dummy INT;

   SET NEW.rating = (SELECT rating FROM table_master WHERE id = NEW.master_id);

   IF NEW.rating IS NULL THEN
       SET NEW.rating = (SELECT rating FROM table_master_backup WHERE id = NEW.master_id);
   END IF;

   IF NEW.rating IS NULL THEN
       SELECT `Master ID tidak ditemukan` INTO dummy
       FROM table_log
       WHERE master_id = NEW.master_id AND tanggal = NEW.tanggal;
   END IF;

END;//
DELIMITER ;

Apabila Anda mencoba melakukan query INSERT dengan data master_id yang tidak ada di table_master dan table_master_backup, maka yang terjadi adalah:

mysql> INSERT INTO table_log(tanggal, master_id) VALUES('2012-11-25', 25);
ERROR 1054 (42S22): Unknown column 'Master ID tidak ditemukan' in 'field list'

Penutup

Trik penggunaan Trigger dibandingkan pengecekan melalui script ada banyak sekali manfaatnya. Di antaranya adalah memusatkan business logic menjadi satu di database. Selain itu juga dengan pengecekan yang di lakukan di sisi database, maka mengurangi traffic data antara database dengan script. Seberapa signifikan? Saya sendiri kurang mengerti, karena belum melakukan penelitian mengenai hal ini :D. Selamat menikmati.

Sumber: Bisa Komputer