Belajar PostgreSQL: Penanganan Data Dengan Trigger - Part 3

Mungkin disini bagian yg sudah bisa saya anggap familiar ketika berhubungan dengan trigger. Ya, sebuah fungsi yang secara otomatis akan dieksekusi dengan ketentuan yang sudah disesuaikan, inilah yg dikenal dengan istilah Trigger dalam sebuah database server.

Mari kita langsung ke praktek, kita buat sebuah tabel baru sbb:

CREATE TABLE tb_stok_buah (
   nama_buah text PRIMARY KEY,
   stok_buah integer NOT NULL,
   jml_pemesanan integer NOT NULL DEFAULT 0,
   CHECK (stok_buah between 0 and 1000 ),
   CHECK (jml_pemesanan <= stok_buah)
);

Kegunaan CHECK disini adalah utk memeriksa berdasarkan ketentuan yg diinginkan, jadi pada contoh tabel diatas kita tidak bisa memiliki stok buah lebih dari 1000 dan tidak bisa memiliki nilai negatif serta jumlah pemesanan tidak bisa lebih dari stok. Kita lanjutkan dengan tabel kedua.

CREATE TABLE tb_penawaran_buah (
   id_penawaran serial PRIMARY KEY,
   nama_penerima text,
   tgl_penawaran timestamp default current_timestamp,
   nama_buah text REFERENCES tb_stok_buah,
   jml_penawaran integer
);

Kemudian kita buatkan sebuah fungsi trigger utk proses pemesanan. Utk membuatnya kita terlebih dahulu mebuat sebuah fungsi yg mirip seperti contoh pada artikel sebelumnya hanya saja nilai return adalah berupa trigger.

CREATE OR REPLACE FUNCTION pemesanan_buah () RETURNS trigger AS
$$
BEGIN
   IF TG_OP = 'INSERT' THEN
    UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan + NEW.jml_penawaran
    WHERE
nama_buah = NEW.nama_buah;
   ELSIF TG_OP = 'UPDATE' THEN
    UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan - OLD.jml_penawaran + NEW.jml_penawaran
    WHERE
    nama_buah = NEW.nama_buah;
   ELSIF TG_OP = 'DELETE' THEN
    UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan - OLD.jml_penawaran
    WHERE
    nama_buah = OLD.nama_buah;
   END IF;
   RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Lalu kita perlu utk membuat trigger menjalankan fungsi diatas. Sebelum itu agar tidak bingung, beberapa bagian dari fungsi diatas yaitu TG_OP singkatan dari Trigger Operation dan NEW/OLD merupakan tempat data dari kolom terkait, NEW utk data baru dan OLD utk data lama atau sebelumnya.

Kita buat trigger utk setiap data record tabel pemesanan berubah, berikut ini contohnya:

CREATE TRIGGER manage_pemesanan_buah
AFTER INSERT OR UPDATE OR DELETE
ON tb_penawaran_buah
FOR EACH ROW
EXECUTE PROCEDURE pemesanan_buah();

Sampai disini kita telah selesai, mari kita lakukan ujicoba. Pertama kita tambahkan dulu beberapa buah ke dalam tabel stok:

INSERT INTO tb_stok_buah(nama_buah,stok_buah) VALUES ('NANGKA',500);
INSERT INTO tb_stok_buah(nama_buah,stok_buah) VALUES ('DURIAN',500);

SELECT * FROM tb_stok_buah;
  nama_buah  | stok_buah | jml_pemesanan
-------------+-----------+-----------------
 NANGKA      |   500     |     0
 DURIAN      |   500     |     0
(2 rows)

Contoh data sudah kita buat, selanjutnya kita buat contoh pemesanan sejumlah 100 buah NANGKA utk John:

INSERT INTO tb_penawaran_buah(nama_penerima, nama_buah, jml_penawaran) VALUES ('John','NANGKA',100); SELECT * FROM tb_penawaran_buah;
 id_penawaran | nama_penerima |         tgl_penawaran         | nama_buah | jml_penawaran
--------------+---------------+-------------------------------+------------+----------------
        1     | John          | 2018-10-02 19:58:21.423115   | NANGKA     |            100
(1 row)

Kita sudah buat pemesanan pertama, jika kita lakukan pengecekan kembali di tabel stok maka akan tampak perubahan di kolom jml_pemesanan:

SELECT * FROM tb_stok_buah;
  nama_buah  | stok_buah | jml_pemesanan
-------------+-----------+----------------
 DURIAN      |      500  |        0
 NANGKA      |      500  |      100

Jika kita melakukan koreksi terhadap jumlah pesanan John misalnya dari 100 buah menjadi 115 buah NANGKA maka jumlah pesanan di tabel stok juga tetap akan berubah:

UPDATE tb_penawaran_buah SET jml_penawaran = 115 WHERE id_penawaran = 1;

SELECT * FROM tb_stok_buah;
  nama_buah  | stok_buah | jml_pemesanan
-------------+-----------+--------------
 DURIAN      |      500  |        0
 NANGKA      |      500  |      115

Pada contoh tabel stok, kita menggunakan sebuah constraint yaitu CHECK. Ada beragam jenis constraint, bisa di lihat disini.

Kembali kita lanjutkan utk contoh query berikutnya, kita coba buat query dimana kita ingin menjual NANGKA yg telah dipesan.

UPDATE tb_stok_buah SET stok_buah = 100 WHERE nama_buah = 'NANGKA';
ERROR: new row for relation "tb_stok_buah" violates check constraint "tb_stok_buah_check"
DETAIL: Failing row contains (NANGKA, 100, 115).

Kemudian kita coba utk memesan lebih dari stok yg tersedia.

UPDATE tb_penawaran_buah SET jml_penawaran = 1100 WHERE id_penawaran = 1;
ERROR: new row for relation "tb_stok_buah" violates check constraint "tb_stok_buah_check"
DETAIL: Failing row contains (NANGKA, 500, 1100).
CONTEXT: SQL statement "UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan - OLD.jml_penawaran + NEW.jml_penawaran
WHERE
nama_buah = NEW.nama_buah"
PL/pgSQL function pemesanan_buah() line 8 at SQL statement

Lalu utk kasus ini, jika kita ingin mengirimkan pesanan maka kita hanya perlu menghapusnya.

DELETE FROM tb_penawaran_buah WHERE id_penawaran = 1;
SELECT * FROM tb_stok_buah;
  nama_buah  | stok_buah | jml_pemesanan
-------------+-----------+--------------
 DURIAN      |      500  |        0
 NANGKA      |      500  |        0
(2 rows)

Akan tetapi, penggunaan DELETE disini hanyalah untuk contoh saja, jika kasusnya ada pada aplikasi nyata, maka langkah terbaik ialah menyimpan data pesanan menjadi arsip terlebih dahulu (misal disediakan tabel arsip) sebelum data pesanan dihapus agar aplikasi punya record pesanan yg telah dilakukan.

Saya kira hanya itu contoh Trigger yg bisa saya berikan, dengan contoh kasus yg sederhana dan mungkin akan lebih baik jika diterapkan pada aplikasi nyata. Silahkan isi kolom komentar jika ada yg ingin ditanyakan ataupun koreksi yg perlu dilakukan.