Membuat Trigger Pada Tabel Jual Beli Stok (Tugas 8 Manajemen Dan Administrasi Basis Data)

TUGAS 8 MANAJEMEN DAN ADMINISTRASI BASIS DATA

Dosen Pengampu: Bp. Eko Purwanto

                                                        NAMA : Eko Pranoto Saputro

                                                        NIM     : 160101237

                                                        Smt      : 4 (empat) 

STMIK DUTA BANGSA SURAKARTA

2018

========================================================================

mysql> create database ekopranotosaputro;
Query OK, 1 row affected

mysql> use ekopranotosaputro;
Database changed

mysql> create table beli(id_beli int(11) primary key auto_increment not null,
kd_barang varchar(10),satuan double,jumlah int(11),
total double,status varchar(30));

Query OK, 0 rows affected

mysql> desc beli;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id_beli   | int(11)     | NO   | PRI | NULL    | auto_increment |
| kd_barang | varchar(10) | YES  |     | NULL    |                |
| satuan    | double      | YES  |     | NULL    |                |
| jumlah    | int(11)     | YES  |     | NULL    |                |
| total     | double      | YES  |     | NULL    |                |
| status    | varchar(30) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set

mysql> create table jual(id_jual int(11) primary key auto_increment not null,
Kd_client varchar(10) not null
,kd_barang varchar(10),Satuan int(5),jumlah double,status varbinary(25));

Query OK, 0 rows affected

mysql> desc jual;
+-----------+---------------+------+-----+---------+----------------+
| Field     | Type          | Null | Key | Default | Extra          |
+-----------+---------------+------+-----+---------+----------------+
| id_jual   | int(11)       | NO   | PRI | NULL    | auto_increment |
| Kd_client | varchar(10)   | NO   |     | NULL    |                |
| kd_barang | varchar(10)   | YES  |     | NULL    |                |
| Satuan    | int(5)        | YES  |     | NULL    |                |
| jumlah    | double        | YES  |     | NULL    |                |
| status    | varbinary(25) | YES  |     | NULL    |                |
+-----------+---------------+------+-----+---------+----------------+
6 rows in set

mysql> create table stok(kd_barang varchar(5) primary key not null,
Jumlah int(11) not null);

Query OK, 0 rows affected

mysql> desc stok;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| kd_barang | varchar(5) | NO   | PRI | NULL    |       |
| Jumlah    | int(11)    | NO   |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
2 rows in set

mysql> insert into stok(kd_barang,Jumlah) values ('16001','50'),('16002','40');

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from stok;
+-----------+--------+
| kd_barang | Jumlah |
+-----------+--------+
| 16001     |     50 |
| 16002     |     40 |
+-----------+--------+
2 rows in set

mysql> create trigger jual after insert on jual for each row
    -> begin
    -> update stok set jumlah=jumlah-new.jumlah where kd_barang=new.kd_barang;
    -> end;
Query OK, 0 rows affected

mysql> insert into jual(kd_client,kd_barang,satuan,jumlah,status) values 
('1001','16001','KG','10','');

Query OK, 1 row affected

mysql> select * from stok;
+-----------+--------+
| kd_barang | Jumlah |
+-----------+--------+
| 16001     |     40 |
| 16002     |     40 |
+-----------+--------+
2 rows in set

mysql> create trigger beli after insert on beli for each row
    -> begin
    -> update stok set jumlah=jumlah+new.jumlah where kd_barang=new.kd_barang;
    -> end;
Query OK, 0 rows affected

mysql> insert into beli(kd_barang,satuan,jumlah,total,status) values 
('16002','KG','10','10','');
Query OK, 1 row affected

mysql> select * from stok;
+-----------+--------+
| kd_barang | Jumlah |
+-----------+--------+
| 16001     |     40 |
| 16002     |     50 |
+-----------+--------+
2 rows in set

mysql>