Adityads Blogs
22:54
0
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.
- Membuat Trigger Untuk membuat trigger caranya adalah dengan mengeksekusi script sql seperti dibawah ini :
- 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.
- Menghapus Trigger Untuk menghapus 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
$$DROP TRIGGER `nama_trigger`;SHOW TRIGGERS;
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);- 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.
- 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 :
- 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 :
- 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 :
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
$$
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;
ENDCREATE 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
