Skip to content

Instantly share code, notes, and snippets.

@mishbah
Forked from hidayat365/trigger-demo.sql
Created November 10, 2013 09:48
Show Gist options
  • Save mishbah/7396156 to your computer and use it in GitHub Desktop.
Save mishbah/7396156 to your computer and use it in GitHub Desktop.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\Nur Hidayat>d:
D:\>cd xampp\mysql\bin
D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.16 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> use test;
Database changed
mysql> -----------------------------
mysql> -- buat table header
mysql> -----------------------------
mysql> create table header (
-> id int auto_increment primary key,
-> amount int default 0
-> ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> -----------------------------
mysql> -- buat table detail
mysql> -----------------------------
mysql> create table detail (
-> id int auto_increment primary key,
-> header_id int not null,
-> quantity int default 0,
-> unit_price int default 0,
-> foreign key (header_id) references header (id)
-> ) ;
Query OK, 0 rows affected (0.03 sec)
mysql> -----------------------------
mysql> -- insert sampel data ke header
mysql> -- kemudian tampilkan
mysql> -----------------------------
mysql> insert into header(amount)
-> values (0), (0), (0) ;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from header ;
+----+--------+
| id | amount |
+----+--------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
+----+--------+
3 rows in set (0.00 sec)
mysql> -----------------------------
mysql> -- buat insert trigger untu tabel detail
mysql> -- amount di table header dihitung
mysql> -- berdasarkan perkalian quantity*unit_price
mysql> -----------------------------
mysql> delimiter |
mysql> create trigger trg_detail_insert
-> after insert on detail
-> for each row
-> begin
-> update header
-> set amount = (
-> select sum(quantity*unit_price)
-> from detail where header_id = new.header_id )
-> where id = new.header_id ;
-> end;
-> |
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> -----------------------------
mysql> -- insert sampel data ke detail
mysql> -- kemudian tampilkan
mysql> -----------------------------
mysql> insert into detail (header_id, quantity, unit_price)
-> values (1, 10, 100), (1, 2, 300),
-> (2, 1, 500), (2, 5, 300) ;
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from detail;
+----+-----------+----------+------------+
| id | header_id | quantity | unit_price |
+----+-----------+----------+------------+
| 3 | 1 | 10 | 100 |
| 4 | 1 | 2 | 300 |
| 5 | 2 | 1 | 500 |
| 6 | 2 | 5 | 300 |
+----+-----------+----------+------------+
4 rows in set (0.00 sec)
mysql> -----------------------------
mysql> -- coba tampilkan isi table header
mysql> -- seharusnya amount sudah berubah
mysql> -----------------------------
mysql> select * from header;
+----+--------+
| id | amount |
+----+--------+
| 1 | 1600 |
| 2 | 2000 |
| 3 | 0 |
+----+--------+
3 rows in set (0.00 sec)
mysql> -----------------------------
mysql> -- YESS....... !!!
mysql> -- it works
mysql> -----------------------------
mysql>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment