Aplikasi Inventory Sederhana Penggunaan Trigger untuk Tracking Stock Barang 3

DELETE Data Transaksi

Untuk proses DELETE, sama seperti proses INSERT, trigger akan berjalan FOR EACH ROW, namun row yang sedang kita hapus ada di variable OLD. Logikanya adalah seperti ini,

• Setiap kali ada penghapusan data transaksi barang masuk, maka field stok barang di table items akan kita kurangi, 
• Sebaliknya, setiap kali ada penghapusan data transaksi barang keluar, maka field stok barang di table items akan kita tambahkan,

maka trigger DELETE akan seperti ini,

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

mysql> delimiter $$   mysql> drop trigger if exists trg_transaction_details_after_delete$$ Query OK, 0 rows affected (0.06 sec)   mysql> -- ----------------------------- mysql> -- after delete trigger mysql> -- ----------------------------- mysql> create trigger trg_transaction_details_after_delete     -> after delete on transaction_details     -> for each row     -> begin     ->   -- -----------------------------     ->   -- ambil jenis transaksi     ->   -- -----------------------------     ->   declare tipe varchar(20);     ->   set tipe = (     ->     select tt.code     ->       from transactions tr     ->       join transaction_types tt on tr.type_id=tt.id     ->       where tr.id=old.trans_id     ->   ) ;     ->   -- -----------------------------     ->   -- update sesuai jenis transaksi     ->   -- -----------------------------     ->   update items     ->     set quantity_on_hand     ->         = quantity_on_hand     ->         + case when tipe='IN' then -old.quantity     ->                when tipe='OUT' then old.quantity     ->                else 0 end     ->     where items.id=old.item_id ;     -> end$$ Query OK, 0 rows affected (0.23 sec)   mysql> delimiter ; mysql>

HideSpoiler: Hasil Test DELETE Transaksi Barang Masuk dan Barang Keluar

Status database sebelum proses DELETE.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> ---------------------------- mysql> -- status stok mysql> -- sebelum proses DELETE mysql> ----------------------------- mysql> select * from items ; +----+--------+-------------------------+------------------+---------+ | id | code   | name                    | quantity_on_hand | remarks | +----+--------+-------------------------+------------------+---------+ |  1 | PC.001 | PC Jangkrik Tipe 1      |            25.00 | NULL    | |  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    | |  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    | |  4 | SV.002 | Server Rainer Rackmount |            10.00 | NULL    | +----+--------+-------------------------+------------------+---------+ 4 rows in set (0.00 sec)   mysql> ----------------------------- mysql> -- status detil transaksi mysql> -- sebelum proses DELETE mysql> ----------------------------- mysql> select * from transaction_details; +----+----------+---------+----------+----------------------+ | id | trans_id | item_id | quantity | remarks              | +----+----------+---------+----------+----------------------+ | 22 |        1 |       1 |       50 | Penerimaan Barang 1  | | 23 |        1 |       2 |       30 | Penerimaan Barang 2  | | 24 |        1 |       3 |       10 | Penerimaan Barang 3  | | 25 |        1 |       4 |       15 | Penerimaan Barang 4  | | 29 |        2 |       1 |       25 | Pengeluaran Barang 1 | | 30 |        2 |       4 |        5 | Pengeluaran Barang 4 | +----+----------+---------+----------+----------------------+ 6 rows in set (0.00 sec)

Test DELETE data transaksi.

Untuk test delete, kita lakukan sederhanan saja, yaitu hapus semua data transactions_details sehingga seluruh data transaksi akaterhapus, maka status stok seharusnya berubah menjadi seperti awal lagi yaitu seluruh saldo stok bernilai 0 (nol)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

mysql> ----------------------------- mysql> -- hapus detil transaksi mysql> ----------------------------- mysql> delete from transaction_details ; Query OK, 4 rows affected (0.03 sec)   mysql> ----------------------------- mysql> -- pastikan table detil transaksi kososng mysql> ----------------------------- mysql> select * from transaction_details; Empty set (0.00 sec)   mysql> ----------------------------- mysql> -- status saldo stok kembali nol mysql> ----------------------------- mysql> select * from items; +----+--------+-------------------------+------------------+---------+ | id | code   | name                    | quantity_on_hand | remarks | +----+--------+-------------------------+------------------+---------+ |  1 | PC.001 | PC Jangkrik Tipe 1      |             0.00 | NULL    | |  2 | PC.002 | PC Jangkrik Tipe 2      |             0.00 | NULL    | |  3 | SV.001 | Server Rainer Tipe 1    |             0.00 | NULL    | |  4 | SV.002 | Server Rainer Rackmount |             0.00 | NULL    | +----+--------+-------------------------+------------------+---------+ 4 rows in set (0.00 sec)   mysql> ----------------------------- mysql> -- YAY!!! I'm Invincible!!! mysql> ----------------------------- mysql>

HideSpoiler: Source Code Trigger Lengkap

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105

delimiter $$   drop trigger if exists trg_transaction_details_after_insert$$ -- ----------------------------- -- after insert trigger -- ----------------------------- create trigger trg_transaction_details_after_insert after insert on transaction_details for each row begin   -- -----------------------------   -- ambil jenis transaksi   -- -----------------------------   declare tipe varchar(20);   set tipe = (     select tt.code       from transactions tr       join transaction_types tt on tr.type_id=tt.id       where tr.id=new.trans_id   ) ;   -- -----------------------------   -- update sesuai jenis transaksi   -- -----------------------------   update items     set quantity_on_hand         = quantity_on_hand         + case when tipe='IN' then new.quantity                when tipe='OUT' then -new.quantity                else 0 end     where items.id=new.item_id ; end$$    drop trigger if exists trg_transaction_details_after_update$$ -- ----------------------------- -- after update trigger -- ----------------------------- create trigger trg_transaction_details_after_update after update on transaction_details for each row begin   -- -----------------------------   -- ambil jenis transaksi   -- -----------------------------   declare tipe varchar(20);   set tipe = (     select tt.code       from transactions tr       join transaction_types tt on tr.type_id=tt.id       where tr.id=old.trans_id   ) ;   -- -----------------------------   -- update sesuai jenis transaksi   -- => kurangi dengan old quantity   -- -----------------------------   update items     set quantity_on_hand         = quantity_on_hand         + case when tipe='IN' then -old.quantity                when tipe='OUT' then old.quantity                else 0 end     where items.id=old.item_id ;   -- -----------------------------   -- update sesuai jenis transaksi   -- => tambahkan dengan new quantity   -- -----------------------------   update items     set quantity_on_hand         = quantity_on_hand         + case when tipe='IN' then new.quantity                when tipe='OUT' then -new.quantity                else 0 end     where items.id=new.item_id ; end$$    drop trigger if exists trg_transaction_details_after_delete$$ -- ----------------------------- -- after delete trigger -- ----------------------------- create trigger trg_transaction_details_after_delete after delete on transaction_details for each row begin   -- -----------------------------   -- ambil jenis transaksi   -- -----------------------------   declare tipe varchar(20);   set tipe = (     select tt.code       from transactions tr       join transaction_types tt on tr.type_id=tt.id       where tr.id=old.trans_id   ) ;   -- -----------------------------   -- update sesuai jenis transaksi   -- -----------------------------   update items     set quantity_on_hand         = quantity_on_hand         + case when tipe='IN' then -old.quantity                when tipe='OUT' then old.quantity                else 0 end     where items.id=old.item_id ; end$$    delimiter ;

Simpulan

Setelah kita perhatikan hasil test di atas, maka proses pengerjaan coding di aplikasi akan jauh lebih mudah karena tidak perlu lagi memikirkan code untuk mengubah status stok barang, karena perubahan status stok barang seluruhnya sudah ditangani oleh trigger yang kita buat ini.