Penggunaan Trigger u/ Inventory

Dosen :     Bp. Allen Boby Hartanto

MATKUL  : Pemrograman SQL

® by Echa






A.      Membuat Database
     CREATE DATABASE db_toko;

B.      Tabel Staff
CREATE TABLE `db_toko`.`staff`
( `id_staff` VARCHAR( 10 ) NOT NULL ,
 `nama_staff` VARCHAR( 60 ) NOT NULL ,
 `username` VARCHAR( 60 ) NOT NULL ,
 `password` VARCHAR( 60 ) NOT NULL , PRIMARY KEY ( `id_staff` ) ) ;



Edit Tabel Staff

ALTER TABLE `staff` ADD `jenis_kelamin` ENUM(‘p’,‘w’) NOT NULL AFTER `password`, ADD `tgl_lahir` DATE NOT NULL AFTER `jenis_kelamin`, ADD `alamat` VARCHAR(100) NOT NULL AFTER tgl_lahir`;


C.      Tabel Pelanggan
CREATE TABLE `db_toko`.`pelanggan` (
`id_pelanggan` VARCHAR( 6 ) NOT NULL ,
`nama_pelanggan` VARCHAR( 60 ) NOT NULL ,
`alamat` VARCHAR( 100) NOT NULL ,
`telephone` VARCHAR( 12 ) NOT NULL ,
PRIMARY KEY ( `id_pelanggan` ) )


D.      Tabel Barang
CREATE TABLE `db_toko`.`barang` (
`kode_barang` VARCHAR( 6 ) NOT NULL ,
`nama_barang` VARCHAR( 100 ) NOT NULL ,
`stok` INT(100) NOT NULL ,
`satuan` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `kode_barang` ) )

E.   Tabel Penjualan
Create table penjualan (
id_penjualan VARCHAR(6) PRIMARY KEY NOT NULL,
id_staff VARCHAR(10) not null,
id_pelanggan VARCHAR(6) not null,
tgl_strk datetime not null,
constraint fk_penjualan_staff Foreign key(id_staff) references staff(id_staff),
constraint fk_penjualan_pelanggan Foreign key(id_pelanggan) references pelanggan(id_pelanggan) )
F.  Tabel Detail Penjualan
CREATE TABLE detail_penjualan(
id_trns int NOT NULL AUTO_INCREMENT primary key,
id_penjualan VARCHAR(6) NOT NULL ,
kode_barang VARCHAR(6) NOT NULL ,
qty int(20) NOT NULL ,
harga decimal(17,2) NOT NULL,
total decimal(17,2) NULL,
constraint fk_detail_penjualan Foreign key(id_penjualan) references penjualan(id_penjualan), constraint fk_detail_barang Foreign key(kode_barang) references barang(kode_barang) )
=======================ISI TABEL ========================
A. Insert Data Tabel Staff
INSERT INTO `db_toko`.`staff` (`id_staff`, `nama_staff`, `username`, `password`, `jenis_kelamin`, `tgl_lahir`, `alamat`) VALUES
('0001', 'Deswa', 'deswa_wa', '123', 'p', '1989-05-05', 'Puri Kembangan'),
('0002', 'Ririn', 'rina_rinarin', '345', 'w', '1992-11-04', 'Margonda Depok')
('0003', 'Irwan', 'wawan25', '789', 'p', '1990-05-05', 'Grogol Jaya'),
('0004', 'Sukijan', 'kiki_ij', '4321', 'p', ' 1990-05-05', 'Meruya Utara')
('0005', 'Sukirman', 'Uki_juki', '5632', 'p', '1993-08-07', 'Cawang');
B. . Insert Data Tabel Pelanggan

INSERT INTO `db_toko`.`pelanggan` (`id_pelanggan`, `nama_pelanggan`, `alamat`, `telephone`) VALUES
(’10001′, ‘Reno’, ‘Serpong ‘, ’08121298821′),
(’10002′, ‘Kirey’, ‘Pasar Minggu’, ’08321678908′)
(’10003′, ‘Evan’, ‘Manggarai’, ’08215643789′),
(’10004′, ‘Dimas’, ‘Bogor’, ’087653423245′)
(’10005′, ‘Jihan’, ‘Lenteng Agung’, ’5769809090′);
C. . Insert Data Tabel Barang

INSERT INTO `db_toko`.`barang` (`kode_barang`, `nama_barang`, `stok`, `satuan`)
VALUES
(‘B0001′, ‘Detergen’, ’100′, ‘pcs’),
(‘B0002′, ‘Minyak goreng’, ’50′, ‘pouch’)
(‘B0003′, ‘Pulpen’, ’70′, ‘pack’),
(‘B0004′, ‘Mie’, ’200′, ‘bh’)
(‘B0005′, ‘Sasa’, ’80′, ‘bh’),
(‘B0006′, ‘Aqua kecil’, ’200′, ‘btl’)
(‘B0007′, ‘Es krim’, ’50′, ‘bh’),
(‘B0008′, ‘Gula pasir’, ’300′, ‘kg’)
(‘B0009′, ‘Vaselin uk. 200 ml’, ’80′, ‘btl’),
(‘B0010′, ‘Coklat 100 gr’, ’65′, ‘bh’);
D.  Insert Data Transaksi penjualan
INSERT INTO `db_toko`.`penjualan` (`id_penjualan`, `id_staff`, `id_pelanggan`, `tgl_strk`) VALUES (‘P001′, ’0001′, ’10005′, ’2015-05-05 09:08:36′),
(‘P002′, ’0002′, ’10004′, ’2015-02-02 14:08:22′)
(‘P003′, ’0003′, ’10003′, ’2015-02-03 19:24:03′),
(‘P004′, ’0004′, ’10002′, ’2015-01-22 14:20:50′)
(‘P005′, ’0005′, ’10001′, ’2015-03-11 18:19:03′);
E.  Insert Tabel Detail Penjualan
INSERT INTO `db_toko`.`detail_penjualan`
(`id_trns`, `id_penjualan`, `kode_barang`, `qty`, `harga`, `total`) VALUES
(’2′, ‘P002′, ‘B0010′, ’3′, ’50000′, NULL),
(’3′, ‘P003′, ‘B0009′, ’7′, ’56000′, NULL)
(’4′, ‘P004′, ‘B0008′, ’12′, ’12000′, NULL),
(’5′, ‘P005′, ‘B0007′, ’15′, ’60000′, NULL)
(’6′, ‘P003′, ‘B0005′, ’2′, ’50000′, NULL),
(’7′, ‘P001′, ‘B0004′, ’8′, ’10000′, NULL),
(’8′, ‘P002′, ‘B0006′, ’17′, ’25000.99′, NULL),
(’9′, ‘P002′, ‘B0002′, ’20′, ’5000.95′, NULL)
(’10′, ‘P002′, ‘B0001′, ’10′, ’18000′, NULL);
=================Membuat Trigger=================


≫ Triger Insert

DROP TRIGGER IF EXISTS `TG_jual`;
CREATE TRIGGER `TG_jual` AFTER INSERT ON `detail_penjualan`
FOR EACH ROW BEGIN UPDATE barang set stok = stok-NEW.qty WHERE kode_barang=NEW.kode_barang; END
DELIMITER $$

≫Trigger Update
CREATE TRIGGER `djual_after_upd_tr`
AFTER UPDATE ON `detail_penjualan`
FOR EACH ROW BEGIN UPDATE barang
SET stok = stok - NEW.qty + OLD.qty WHERE kode_Barang = OLD.kode_Barang; END;
DELIMITER $$

≫Trigger Delete
CREATE TRIGGER `TG_delete`
BEFORE DELETE ON `detail_penjualan`
FOR EACH ROW BEGIN UPDATE barang
SET stok = stok + OLD.qty WHERE kode_barang = OLD.kode_Barang; END
DELIMITER $$





≫ Trigger Penjumlahan Harga

CREATE TRIGGER `TG_total_harga`
BEFORE INSERT ON detail_penjualan
FOR EACH ROW BEGIN SET new.Total = new.qty * new.harga; END

DELIMITER $$


============TAMPILAN SESUDAH TRIGGER===================

=========MEMBUAT TAMPILAN DATA SBB========

 

ID_penjualan	Tgl_penjualan	Jumlah Harga	Nama Pelanggan	Nama Staff	Total Barang

 

SELECT penjualan.id_penjualan, penjualan.tgl_strk as tanggal_penjualan, sum(detail_penjualan.total) as Jumlah_penjualan,pelanggan.nama_pelanggan, staff.nama_staff,sum(detail_penjualan.qty)as Total_Barang FROM penjualan,detail_penjualan,pelanggan, staff
WHERE penjualan.id_penjualan=detail_penjualan.id_penjualan
AND pelanggan.id_pelanggan=penjualan.id_pelanggan AND staff.id_staff =penjualan.id_staff
GROUP BY penjualan.id_penjualan