Percobaan Trigger

Best Rapidshare Search

Microsoft Windows [Version 6.2.9200]
(c) 2012 Microsoft Corporation. All rights reserved.

C:\Users\naufal>CD\

C:\>CD xampp/mysql/bin

C:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database cobatransaksi;
Query OK, 1 row affected (0.00 sec)

mysql> use cobatransaksi;
Database changed
mysql> create table barang(
    -> brg_kode int(11) not null,
    -> brg_nama char(30),
    -> brg_satuan char(20),
    -> brg_jml smallint(5),
    -> constraint pk_brg_kode primary key (brg_kode)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> create table transaksi(
    -> id int(11) primary key,
    -> kd_brg int(11),
    -> jml smallint(5),
    -> constraint fk_kdbrg foreign key (kd_brg) references barang (brg_kode)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> create table stok(
    -> id int(11) primary key,
    -> tgl char(30),
    -> nama char(30),
    -> sisa_stok smallint(5)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+-------------------------+
| Tables_in_cobatransaksi |
+-------------------------+
| barang                  |
| stok                    |
| transaksi               |
+-------------------------+
3 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create trigger stock_tgr after insert on transaksi for each row begin
    -> insert into stok (tgl, nama, sisa_stok) values (now(),(select barang.brg_nama
    -> from barang where new.kd_brg=barang.brg_kode),(select barang.brg_jml from barang
    -> where new.kd_brg=barang.brg_kode)-new.jml);
    -> end; $$
Query OK, 0 rows affected (0.28 sec)

mysql> insert into barang (brg_kode,brg_nama,brg_satuan,brg_jml) values (3,'barangxx','inchi',2);
    -> delimiter$$
Query OK, 1 row affected (0.08 sec)

mysql> show tables;
    -> $$
+-------------------------+
| Tables_in_cobatransaksi |
+-------------------------+
| barang                  |
| stok                    |
| transaksi               |
+-------------------------+
3 rows in set (0.06 sec)

mysql> select*from barang;$$
+----------+----------+------------+---------+
| brg_kode | brg_nama | brg_satuan | brg_jml |
+----------+----------+------------+---------+
|        3 | barangxx | inchi      |       2 |
+----------+----------+------------+---------+
1 row in set (0.00 sec)

mysql> insert into transaksi (kd_brg,jml) values (3,1);$$
Query OK, 1 row affected, 1 warning (0.14 sec)

mysql> select*from barang;$$
+----------+----------+------------+---------+
| brg_kode | brg_nama | brg_satuan | brg_jml |
+----------+----------+------------+---------+
|        3 | barangxx | inchi      |       2 |
+----------+----------+------------+---------+
1 row in set (0.00 sec)


mysql> select*from transaksi;$$
+----+--------+------+
| id | kd_brg | jml  |
+----+--------+------+
|  0 |      3 |    1 |
+----+--------+------+
1 row in set (0.00 sec)

mysql> select*from stok;$$
+----+---------------------+----------+-----------+
| id | tgl                 | nama     | sisa_stok |
+----+---------------------+----------+-----------+
|  0 | 2013-06-25 12:41:12 | barangxx |         1 |
+----+---------------------+----------+-----------+
1 row in set (0.00 sec)

mysql>

DELIMITER $$

USE `cobatransaksi`$$

DROP TRIGGER `update_trigger`$$

CREATE

    TRIGGER `update_trigger` AFTER UPDATE ON `suplier`
    FOR EACH ROW BEGIN
    UPDATE barang SET brg_jml=new.jml_brg WHERE brg_kode=new.id_brg;
    END;
$$

DELIMITER ;

mysql>

DELIMITER $$

USE `cobatransaksi`$$

DROP TRIGGER  `trigger_delete`$$

CREATE
       TRIGGER `trigger_delete` AFTER DELETE ON `transaksi`
    FOR EACH ROW BEGIN
    DELETE FROM stok WHERE id=old.id;
    END;
$$

DELIMITER ;

mysql>

USE `cobatransaksi`$$

DROP VIEW IF EXISTS `view_transaksi`$$

CREATE VIEW `view_transaksi` AS
SELECT
  `stok`.`tgl`         AS `tgl`,
  `transaksi`.`kd_brg` AS `kd_brg`,
  `stok`.`nama`        AS `nama`,
  `transaksi`.`jml`    AS `jml`,
  `stok`.`sisa_stok`   AS `sisa_stok`
FROM (`transaksi`
   LEFT JOIN `stok`
     ON ((`transaksi`.`id` = `stok`.`id`)))$$

DELIMITER ;

Socializer Widget By Blogger Yard

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →