Demonstrasi Trigger AFTER INSERT untuk mengubah field amount di table header berdasarkan insert ke table detail

trigger-demo.sql

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>