Latihan membuat Trigger (melalui mysql)
mysql> create database simpan_pinjam;
Query OK, 1 row affected (0.05 sec)
mysql> use simpan_pinjam;
Database changed
mysql> create table nasabah(no_rek int(11), no_ktp int(8), nama char(10), alamat
char(10), saldo int(10));
Query OK, 0 rows affected (0.50 sec)
mysql> insert into nasabah values
-> ('1010123001','1099901','budi','plaju','1000000'),
-> ('1010123002','1099902','anton','pusri','4500000'),
-> ('1010123003','1099903','toni','kertapati','5000000');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select*from nasabah;
+----------------+------------+---------+-----------+------------+
| no_rek | no_ktp | nama | alamat | saldo |
+----------------+------------+---------+-----------+------------+
| 1010123001 | 1099901 | budi | plaju | 1000000 |
| 1010123002 | 1099902 | anton | pusri | 4500000 |
| 1010123003 | 1099903 | toni | kertapati | 5000000 |
+----------------+------------+---------+------------+-----------+
3 rows in set (0.00 sec)
mysql> create table transaksi(no_trans int(3),no_rek int(11),nama char(10),debet
int(10),kredit int(10));
Query OK, 0 rows affected (0.39 sec)
mysql> insert into transaksi values
-> ('001','1010123001','budi','1000000','-'),
-> ('002','1010123002','anton','4500000','-'),
-> ('003','1010123003','toni','5000000','-'),
-> ('004','1010123003','toni','250000','-'),
-> ('005','1010123002','anton','-','500000'),
-> ('006','1010123001','budi','550000','-'),
-> ('007','1010123002','anton','-','150000');
Query OK, 7 rows affected, 7 warnings (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select*from transaksi;
+----------+---------------+-------+-----------+---------+
| no_trans | no_rek | nama | debet | kredit |
+----------+---------------+-------+-----------+---------+
| 1 | 1010123001 | budi | 1000000 | 0 |
| 2 | 1010123002 | anton | 4500000 | 0 |
| 3 | 1010123003 | toni | 5000000 | 0 |
| 4 | 1010123003 | toni | 250000 | 0 |
| 5 | 1010123002 | anton | 0 | 500000 |
| 6 | 1010123001 | budi | 550000 | 0 |
| 7 | 1010123002 | anton | 0 | 150000 |
+----------+--------------+--------+-----------+---------+
7 rows in set (0.00 sec)
mysql> delimiter $
mysql> create trigger auto_insert_nasabah_backup
-> before insert on nasabah for each row
-> begin
-> insert into nasabah_backup(
-> no_rek,
-> no_ktp,
-> nama,
-> alamat,
-> saldo)
-> values(
-> new.no_rek,
-> new.no_ktp,
-> new.nama,
-> new.alamat,
-> new.saldo);
-> end $
Query OK, 0 rows affected (0.39 sec)
mysql> delimiter $
mysql> create trigger hapus_nasabah after delete
-> on nasabah for each row
-> begin
-> insert into nasabah_backup
-> (no_rek,
-> no_ktp,
-> nama,
-> alamat,
-> saldo)
-> values
-> (old.no_rek,
-> old.no_ktp,
-> old.nama,
-> old.alamat,
-> old.saldo);
-> end;
-> $
Query OK, 0 rows affected (0.02 sec)
mysql> select*from transaksi;
+----------+--------------+-------+----------+--------+
| no_trans | no_rek | nama | debet | kredit |
+----------+--------------+-------+----------+--------+
| 1 | 1010123001 | budi | 1000000 | 0 |
| 2 | 1010123002 | anton | 4500000 | 0 |
| 3 | 1010123003 | toni | 5000000 | 0 |
| 4 | 1010123003 | toni | 250000 | 0 |
| 5 | 1010123002 | anton | 0 | 500000 |
| 6 | 1010123001 | budi | 550000 | 0 |
| 7 | 1010123002 | anton | 0 | 150000 |
+----------+--------------+-------+---------+---------+
7 rows in set (0.00 sec)
mysql> delimiter $
mysql> create trigger auto_insert_transaksi_backup
-> before insert on transaksi for each row
-> begin
-> insert into transaksi_backup
-> (no_trans,
-> no_rek,
-> nama,
-> debet,
-> kredi)
-> values
-> (new.no_trans,
-> new.no_rek,
-> new.nama,
-> new.debet,
-> new.kredit);
-> end $
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter $
mysql> create trigger hapus_transaksi after delete
-> on transaksi for each row
-> begin
-> insert into transaksi_backup
-> (no_trans,
-> no_rek,
-> nama,
-> debet,
-> kredit)
-> values
-> (old.no_trans,
-> old.no_rek,
-> old.nama,
-> old.debet,
-> old.kredit);
-> end $
Query OK, 0 rows affected (0.01 sec)
Tidak ada komentar:
Posting Komentar