TUGAS 7
MANAJEMEN BASIS DATA
"Membuat dan Menampilakn TRIGGER"
Disusun Oleh :
INGGIT NADYA NASTITI
160101243
STMIK DUTA BANGSA SURAKARTA
2018
______________________________________________
mysql> create table beli (
-> id_beli int (11) not null auto_increment primary key,
-> kd_barang varchar (10),
-> satuan double,
-> jumlah int (11),
-> total double,
-> status varchar (30));
Query OK, 0 rows affected (0.65 sec)
mysql> desc beli;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id_beli | int(11) | NO | PRI | NOT 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 (0.21 sec)
-------------------------------------------------------------------------------
mysql> create table jual (
-> id_jual int (11) auto_increment primary key,
-> kd_client varchar (10),
-> kd_barang varchar (10),
-> satuan int (5),
-> jumlah double,
-> status varbinary(25));
Query OK, 0 rows affected (0.35 sec)
mysql> desc jualL;
+-----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+----------------+
| id_jual | int(11) | NO | PRI | NULL | auto_increment |
| kd_client | varchar(10) | YES | | 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 (0.06 sec)
-------------------------------------------------------------------------------
mysql> create table stok (
-> kd_barang varchar (5) not null primary key,
-> jumlah Int (11) not null );
Query OK, 0 rows affected (0.41 sec)
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 (0.12 sec)
-------------------------------------------------------------------------------
mysql> CREATE TRIGGER beli_barang after INSERT ON beli
-> FOR EACH ROW INSERT INTO stok SET
-> kd_barang=NEW.kd_barang, jumlah=NEW.jumlah ON DUPLICATE KEY
-> UPDATE jumlah=jumlah+NEW.jumlah;
Query OK, 0 rows affected (0.10 sec)
-------------------------------------------------------------------------------
mysql> insert into beli (kd_barang,satuan,jumlah,total,status)
-> values
-> ('A1','','10','',''),
-> ('B2','','20','',''),
-> ('C2','','28','','');
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 6
mysql> SELECT*FROM STOK;
+-----------+--------+
| kd_barang | jumlah |
+-----------+--------+
| A1 | 10 |
| B2 | 20 |
| C2 | 28 |
+-----------+--------+
3 rows in set (0.04 sec)
-------------------------------------------------------------------------------
mysql> CREATE TRIGGER jual_barang AFTER INSERT ON jual FOR EACH ROW
-> UPDATE stok SET jumlah=jumlah-NEW.jumlah WHERE kd_barang=NEW.kd_barang;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into jual (kd_client,kd_barang,satuan,jumlah,status)
-> values
-> ('01','A1','2','5',''),
-> ('01','B2','2','6',''),
-> ('02','C2','3','8','');
Query OK, 3 rows affected (0.25 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select*from stok;
+-----------+--------+
| kd_barang | jumlah |
+-----------+--------+
| A1 | 5 |
| B2 | 14 |
| C2 | 20 |
+-----------+--------+
3 rows in set (0.04 sec)
-------------------------------------------------------------------------------
_______________________________________________________________________________
Komentar
Posting Komentar