AdityaDees

Bismillah...

Pada kasus tertentu, terkadang kita menginginkan sebuah script sql tertentu di eksekusi ketika terjadi proses insert, update ataupun delete di database. Pada aplikasi kasir misalnya, saat kasir menginput barang yang dibeli oleh customer di mesin kasir dan proses transaksi tersebut berhasil dilakukan, maka stok barang yang dibeli tentu akan berkurang. Atau contoh lain seperti anda berbelanja disebuah online shop misalnya, barang atau item yang anda checkout akan otomatis berkurang stoknya di penjual. Nah, sekarang bagaimanakah menerapkan sistem tersebut? Jika terjadi proses seperti ini kita akan melakukan update stok di tabel barang atau item. Mungkin selama ini anda banyak melakukannya di dalam source code aplikasi secara langsung. Memang tidak mengapa melakukan hal seperti itu didalam source code langsung, akan tetapi ada cara yang lebih baik dan efisien yang bisa anda terapkan yakni dengan menggunakan trigger database.

Apa itu trigger, apa fungsi dan manfaatnya ? Trigger adalah kumpulan script sql yang dieksekusi secara otomatis ketika terjadi proses DML (Data Manipulation Language) seperti insert, update dan delete di pada sebuah tabel. Umumnya trigger memiliki opsi apakah script sql akan di eksekusi sebelum atau sesudah, atau kedua-duanya.

Fungsi Trigger :

• Mengeksekusi script sql secara otomatis ketika terjadi perubahan pada sebuah tabel.
• Menjaga integritas data.
• Mencegah proses perubahan yang tidak dibenarkan atau tidak sah.

Manfaat Trigger :

• Aplikasi yang dibuat menjadi lebih cepat jika dibandingkan menulis langsung script sql di kode program.
• Reusable, artinya ketika terjadi migrasi atau perpindahan bahasa pemrograman maka kita tidak perlu menulis ulang script sql di source code aplikasi.
• Mudah untuk di maintenance.

Selanjutnya kita akan membahas tentang cara bagaimana membuat trigger serta menghapusnya. Dan dilanjutkan dengan contoh studi kasus sederhana untuk penerapan pembuatan trigger tersebut.

1. Membuat Trigger
Untuk membuat trigger caranya adalah dengan mengeksekusi script sql seperti dibawah ini :


DELIMITER $$
CREATE TRIGGER `nama_trigger` {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON `nama_tabel`  FOR EACH ROW
BEGIN
# … Bagian script sql yang akan dieksekusi
END
$$

Penjelasan :

• Insert
Trigger ketika insert merupakan sebuah kondisi dimana kita menjalankan script sql ketika perintah insert pada sebuah tabel dieksekusi. Trigger di proses insert terdapat dua buah kejadian, yakni BEFORE dan AFTER. BEFORE akan di eksekusi sebelum perintah insert dijalankan, sedangkan AFTER sendiri akan di eksekusi ketika perintah insert selesai dijalankan. Pada saat membuat trigger, bagian script yang kita eksekusi biasanya perlu mengambil nilai field dari tabel yang berubah tersebut. Cara mengambil nilai field tersebut adalah dengan keyword NEW dan diikuti dengan nama field ditabel.
• Update
Trigger update akan dieksekusi ketika perintah update di jalankan pada sebuah tabel. Sama seperti trigger insert, trigger update juga terdapat dua buah kejadian yakni BEFORE dan AFTER. Perbedaannya adalah trigger update terdapat keyword NEW dan OLD sedangkan trigger insert hanya keyword NEW saja. Dimana keyword NEW digunakan untuk mengambil field tabel yang baru saja diubah nilainya, sedangkan keyword OLD digunakan untuk mengambil nilai field sebelum diubah.
• Delete
Terakhir adalah trigger delete. Trigger ini akan di eksekusi ketika data pada sebuah tabel dihapus. Sama seperti kedua trigger diatas, trigger ini juga memiliki dua buah kejadian. Perbedaannya adalah trigger delete hanya memiliki keyword OLD untuk mengambil nilai field.


2. Menghapus Trigger
Untuk menghapus trigger, caranya adalah dengan mengeksekusi script sql seperti dibawah ini :


DROP TRIGGER `nama_trigger`;

Untuk melihat trigger yang pernah dibuat sebelumnya, caranya adalah dengan mengeksekusi script sql :

SHOW TRIGGERS;

Diakhir postingan ini kita akan mencoba membuat trigger sederhana. Pada kasus ini kita akan mengupdate stok barang secara otomatis ketika barang tersebut dijual, diubah atau dihapus. Sekarang buatlah 2 buah tabel seperti dibawah ini : Untuk mempermudah langsung saja copas script berikut ini untuk membuat tabel diatas, ditutorial ini saya menggunakan MySQL atau Maria DB sebagai database:

CREATE TABLE items (
	id_item INT(10) NOT NULL AUTO_INCREMENT,
	nama_item VARCHAR(100) NULL DEFAULT NULL,
	harga FLOAT(15,2) NULL DEFAULT NULL,
	stok INT(10) NULL DEFAULT NULL,
	PRIMARY KEY (id_item) USING BTREE
);

CREATE TABLE transaction (
	id_transaction INT(10) NOT NULL AUTO_INCREMENT,
	id_item INT(10) NOT NULL,
	qty INT(10) NULL DEFAULT '1',
	total FLOAT(15,2) NULL DEFAULT NULL,
	PRIMARY KEY (id_transaction) USING BTREE,
	INDEX FK__items (id_item) USING BTREE,
	CONSTRAINT FK__items FOREIGN KEY (id_item) REFERENCES items (id_item) ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO `items` (`id_item`, `nama_item`, `harga`, `stok`) VALUES
(1, 'Gula', 11000.00, 10), (2, 'Kopi', 5000.00, 20), (3, 'Susu', 8000.00, 25), (4, 'Indomie', 2500.00, 15);

Dari tabel diatas kita akan membuat trigger dengan rule sebagai berikut :

• Stok di tabel items akan otomatis ter-update pada saat dilakukan insert, update ataupun delete di tabel transaction.
• Sebelum melakukan insert, cek terlebih dahulu stok di tabel items. Dimana stok di tabel items tidak boleh kurang dari 0 pada saat terjadi transaksi di tabel transaction.
• Pada saat melakukan update data di tabel transaction, jika jumlah qty yang baru di masukkan lebih kecil dari qty yang sebelumnya (qty lama), maka stok yang ada di tabel items akan otomatis bertambah, begitu juga sebaliknya stok akan berkurang jika qty baru lebih besar dari qty sebelumnya.
• Pada saat setelah terjadi proses delete, stok di tabel items akan otomatis bertambah.

Dari rule diatas maka kita akan membuat trigger INSERT BEFORE, UPDATE BEFORE dan DELETE AFTER pada tabel transaction.

1. INSERT BEFORE
Trigger yang pertama yang akan kita buat adalah trigger insert before. Trigger ini sendiri akan melakukan pengecekan pada saat ada penambahan baris data baru di tabel transaction, dimana jika qty yang akan dijual dikurangi stok yang ada di item tidak boleh kurang dari nol. Script sql-nya adalah seperti dibawah ini :

DELIMITER $$
CREATE TRIGGER `transaction_insert_before` BEFORE INSERT ON `transaction` FOR EACH ROW BEGIN
SET @stok = (SELECT stok FROM items WHERE id_item = NEW.id_item);
SET @sisa = @stok - NEW.qty;
IF @sisa < 0 THEN
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Warning: stok tidak cukup'; 
END IF;
UPDATE items SET stok = @sisa WHERE id_item = NEW.id_item;
END
$$

Pada script diatas kita membuat trigger dengan nama `transaction_insert_before` ditabel transaction. Pada baris berikutnya kita membuat variabel `@stok` dan mengisinya dengan stok yang ada di tabel items, sesuai dengan id_item yang dimasukkan ditabel transaction. Selanjutnya kita membuat variabel sisa yang isinya adalah hasil pengurangan antara stok dengan qty yang dimasukkan dari tabel transaction. Kemudian dicek apakah hasil pengurangan tadi kurang dari nol? Jika iya, maka kita akan menghentikan operasi insert ditabel transaction dengan melemparkan pesan kesalalahan SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Warning: stok tidak cukup'; . Namun jika sisa tidak kurang dari nol, maka kita akan meng-update stok yang ada ditabel items dengan nilai yang ditampung di variabel sisa tadi. Hasilnya jika kita memasukkan qty melebihi stok yang ada ditabel items, maka akan muncul pesan kesalahan seperti ini pada saat insert transaction :

2. UPDATE BEFORE
Trigger ini akan melakukan pengecekan pada saat tabel transaction melakukan proses update data. Dimana rule yang telah ditentukan diatas, maka script sql untuk membuat trigger update before adalah sebagai berikut :

CREATE  TRIGGER `transaction_update_before` BEFORE UPDATE ON `transaction` FOR EACH ROW 
BEGIN
IF OLD.id_item = NEW.id_item THEN 
	SET @stok = (SELECT stok FROM items WHERE id_item = OLD.id_item);
	SET @sisa = (@stok + OLD.qty) - NEW.qty;
	IF @sisa < 0 THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Warning: stok tidak cukup'; 
	END IF;
	UPDATE items SET stok = @sisa WHERE id_item = OLD.id_item;
ELSE
	SET @stok_lama = (SELECT stok FROM items WHERE id_item = OLD.id_item);
	SET @sisa_lama = (@stok_lama + OLD.qty);
	UPDATE items SET stok = @sisa_lama WHERE id_item = OLD.id_item;
	SET @stok_baru = (SELECT stok FROM items WHERE id_item = NEW.id_item);
	SET @sisa_baru = @stok_baru - NEW.qty;
	IF @sisa_baru < 0 THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Warning: stok tidak cukup'; 
	END IF;
	UPDATE items SET stok = @sisa_baru WHERE id_item = NEW.id_item;
END IF;
END

Saat membuat trigger update, kita melihat bahwa disana ada keyword NEW dan OLD. Keyword NEW disini berarti merupakan nilai sebuah field yang baru saja diubah, sedangkan OLD adalah nilai field yang sebelumnya atau yang lama. Sama seperti trigger insert, kita akan melakukan pengecekan jika kondisi tidak sesuai dengan yang diinginkan maka kita akan melemparkan pesan kesalahan dalam bentuk SIGNAL SQLSTATE '45000' .
3. DELETE AFTER
Terakhir trigger yang akan kita buat adalah trigger delete after. Trigger ini akan dieksekusi setelah proses delete terjadi. Script-nya adalah sebagai berikut :

CREATE TRIGGER `transaction_delete_after` AFTER DELETE ON `transaction` FOR EACH ROW 
BEGIN
SET @stok = (SELECT stok FROM items WHERE id_item = OLD.id_item);
SET @sisa = @stok + OLD.qty;
UPDATE items SET stok = @sisa WHERE id_item = OLD.id_item;
END

Mengikuti rule yang telah dibuat sebelumnya, yakni jika ada baris data yang dihapus pada tabel transaction, maka stok pada tabel items akan bertambah sesuai dengan jumlah qty yang dihapus pada tabel transaction.

Demikian tutorial Trigger Database, Fungsi dan Manfaatnya. Jika ada pertanyaan silahkan ditanyakan dikolom komentar.