DATABASE TRIGGER DAN CONSTRAINT

Tugas ini mengulas tentang manajemen stock atau inventory dan tidak membahas bagaimana menghitung Total ataupun subtotal. Untuk manajemen stock dibuat menggunakan trigger database. Privilege yang dibutuhkan CREATE TRIGGER.
Ada lima tabel yang digunakan yaitu MASTER_BARANG, ORD, ORD_DETAIL, SALES dan SALES_DETAIL. Rancangan tabel seperti pada gambar.
Ketentuan
• Barang yang akan dibeli atau dijual harus ada di tabel MASTER_BARANG
• Pembelian akan menambah stock barang
• Penjualan akan mengurangi stock barang
• Quantity (QTY) penjualan dan pembelian boleh diupdate dan otomatis akan mempengaruhi jumlah stock di MASTER_BARANG
1. MEMBUAT TABEL DAN CONSTRAINT
CREATE TABLE MASTER_BARANG (
KODE_BRG NUMBER(4) PRIMARY KEY,
NAMA_BRG VARCHAR2(20) NOT NULL,
HARGA NUMBER(10),
STOCK NUMBER(4));
CREATE TABLE ORD(
NO_ORDER NUMBER(4) PRIMARY KEY,
ID_SUPPLIER NUMBER(4) NOT NULL,
TGL_ORDER DATE,
RP_TOTAL NUMBER(12));

CREATE TABLE ORDER_DETAIL(
NO_ORDER NUMBER(4) REFERENCES ORD(NO_ORDER),
NO_URUT NUMBER(3),
KODE_BRG NUMBER(4) REFERENCES MASTER_BARANG(KODE_BRG),
HARGA NUMBER(10),
QTY NUMBER(4),
SUBTOTAL NUMBER(10));

CREATE TABLE SALES(
NO_SALES NUMBER(4) PRIMARY KEY,
ID_CUST NUMBER(4) NOT NULL,
TGL_SALES DATE,
RP_TOTAL NUMBER(12));

CREATE TABLE SALES_DETAIL(
NO_SALES NUMBER(4) REFERENCES SALES(NO_SALES),
NO_URUT NUMBER(3),
KODE_BRG NUMBER(4) REFERENCES MASTER_BARANG(KODE_BRG),
HARGA NUMBER(10),
QTY NUMBER(4),
SUBTOTAL NUMBER(10));

2. MEMBUAT TRIGGER DATABASE
Trigger UPDATE_STOCK_BRG_ORD
Buat trigger update_stock_brg_ord di tabel order_detail Yang berfungsi untuk mengupdate stock di tabel master_barang jika ada pembelian barang atau perubahan qty di tabel order_detail.
CREATE OR REPLACE TRIGGER update_STOCK_brg_ord
BEFORE INSERT OR UPDATE OF QTY ON order_detail
FOR EACH ROW
BEGIN
IF NVL(:OLD.QTY,0) < NVL(:NEW.QTY,0) THEN
UPDATE MASTER_BARANG
SET STOCK = STOCK + (NVL(:NEW.QTY,0)-NVL(:OLD.QTY,0))
WHERE KODE_BRG = :NEW.KODE_BRG;
ELSE
UPDATE MASTER_BARANG
SET STOCK = STOCK - (NVL(:OLD.QTY,0)-NVL(:NEW.QTY,0))
WHERE KODE_BRG = :NEW.KODE_BRG;
END IF;
END;
/

Trigger UPDATE_STOCK_BRG_SALES
Buat trigger update_stock_brg_sales di tabel sales_detail yang berfungsi untuk mengupdate stock di tabel master_barang jika ada penjualan barang atau perubahan qty di tabel sales_detail.
CREATE OR REPLACE TRIGGER update_STOCK_brg_sales
BEFORE INSERT OR UPDATE OF QTY ON sales_detail
FOR EACH ROW
BEGIN
IF NVL(:OLD.QTY,0) < NVL(:NEW.QTY,0) THEN
UPDATE MASTER_BARANG
SET STOCK = STOCK - (NVL(:NEW.QTY,0)-NVL(:OLD.QTY,0))
WHERE KODE_BRG = :NEW.KODE_BRG;
ELSE
UPDATE MASTER_BARANG
SET STOCK = STOCK + (NVL(:OLD.QTY,0)-NVL(:NEW.QTY,0))
WHERE KODE_BRG = :NEW.KODE_BRG;
END IF;
END;
/

3. ISI DATA KE TABEL
Isi master_barang dengan beberapa barang dengan kolom stock di set 0 sebagai data awal.
INSERT INTO MASTER_BARANG VALUES(100,’PERMEN’,1000,0);
INSERT INTO MASTER_BARANG VALUES(200,’BUKU’,2500,0);
INSERT INTO MASTER_BARANG VALUES(300,’ROTI’,1500,0);
-- Lihat isi data BARANG
select * from MASTER_BARANG;
KODE_BRG NAMA_BRG HARGA STOCK
---------- ---------- ------ -------
100 PERMEN 1000 0
200 BUKU 2500 0
300 ROTI 1500 0


4. PEMBELIAN, PENJUALAN dan UPDATE
Lakukan pembelian untuk semua barang permen 200, buku 100, roti 100 Proses pembelian disini berarti Anda mengisi data ke tabel ORD dan ORDER_DETAIL
-- Isi dulu tabel ORD
INSERT INTO ORD VALUES(1,100,SYSDATE,600000);

--Isi tabel ORD_DETAIL
INSERT INTO ORDER_DETAIL VALUES(1,2,200,2500,100,250000);
INSERT INTO ORDER_DETAIL VALUES(1,3,300,1500,100,150000);
INSERT INTO ORDER_DETAIL VALUES(1,1,100,1000,200,200000);

--Lihat data di ORD_DETAIL
SELECT * FROM ORDER_DETAIL;

NO_ORDER NO_URUT KODE_BRG HARGA QTY SUBTOTAL
-------- ------- ---------- ------ --- ----------
1 2 200 2500 100 250000
1 3 300 1500 100 150000
1 1 100 1000 200 200000

--Periksa stock pada tabel master_barang
select * from MASTER_BARANG;

KODE_BRG NAMA_BRG HARGA STOCK
---------- ---------- ------ -------
100 PERMEN 1000 200
200 BUKU 2500 100
300 ROTI 1500 100

-- Semua stock barang sudah bertambah sesuai jumlah order.

Lakukan penjualan barang permen 20, buku 10, roti 50. Proses penjualan disini berarti Anda mengisi data ke tabel SALES dan SALES_DETAIL
--Isi tabel SALES
INSERT INTO SALES VALUES (1,222,SYSDATE,120000); 1 row created.

--Isi tabel SALES_DETAIL
INSERT INTO SALES_DETAIL VALUES(1,1,100,1000,20,20000);
INSERT INTO SALES_DETAIL VALUES(1,2,200,2500,10,25000);
INSERT INTO SALES_DETAIL VALUES(1,3,300,1500,50,75000);

--Lihat isi tabel SALES_DETAIL
select * from SALES_DETAIL;

NO_SALES NO_URUT KODE_BRG HARGA QTY SUBTOTAL
-------- ------- -------- ------ ----- --------
1 1 100 1000 20 20000
1 2 200 2500 10 25000
1 3 300 1500 50 75000

--Lihat stock di tabel MASTER_BARANG
select * from MASTER_BARANG;

KODE_BRG NAMA_BRG HARGA STOCK
---------- ---------- ------ -------
100 PERMEN 1000 180
200 BUKU 2500 90
300 ROTI 1500 50

--Stock berkurang sesuai dengan jumlah penjualan.

Demikian pembahasan sederhana tentang manajemen stock (inventory) dengan menggunakan trigger database