Trigger MySQL – Case Study 1


Penjelasan Kasus : Penulis ingin menampilkan rata-rata dari penjualan-penjualan yang dilakukan oleh masing2 sales pada suatu perusahaan

Tabel yg digunakan :

  1. tabel sales : untuk mencatat semua transaksi penjualan sales
  2. tabel performance : untuk mencatat rata-rata (everage) dari seluruh penjualan yg dilakukan oleh sales

Struktur tabel

CREATE TABLE `performance` (
`employee_id` int(11) NOT NULL,
`name` varchar(25) NOT NULL,
`total_sales` decimal(6,2) NOT NULL,
`ave_sale` decimal(6,2) NOT NULL
)

CREATE TABLE `sales` (
`employee_id` tinyint(4) NOT NULL,
`name` varchar(25) NOT NULL,
`date` date NOT NULL,
`sale_amt` decimal(6,2) NOT NULL,
`prod_id` int(11) NOT NULL
)

Buat trigger pada tabel sales :

DELIMITER $$
create trigger `sales_bi_trg` BEFORE INSERT on `sales`
for each row BEGIN
/* sumber : http://www.databasedesign-resource.com/mysql-triggers.html*/
DECLARE num_row INTEGER;
DECLARE tot_rows INTEGER;
select count(*) into tot_rows from sales where employee_id=NEW.employee_id;
select count(*) into num_row from sales where employee_id=NEW.employee_id;
IF num_row > 0 THEN
UPDATE performance
SET total_sales = NEW.sale_amt+total_sales,
ave_sale=total_sales/(tot_rows+1)
WHERE employee_id = NEW.employee_id;
ELSE
INSERT INTO performance(employee_id,name,total_sales,ave_sale)
VALUES(NEW.employee_id,NEW.name,NEW.sale_amt,NEW.sale_amt);
END IF;
END;
$$
DELIMITER ;

Pengujian : Lakukan insert data pada tabel sales lalu lihat perubahan pada tabel performance

insert  into `sales`(`employee_id`,`name`,`date`,`sale_amt`,`prod_id`)
values (1,’blackphp’,’2009-10-22′,’600.00′,1),
(1,’blackphp’,’2009-10-23′,’50.00′,2),
(2,’Ari’,’2009-10-22′,’200.00′,3),
(2,’Ari’,’2009-10-24′,’100.00′,4),
(2,’Ari’,’2009-10-25′,’300.00′,5);
Thnks… Salam DBA🙂

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on October 21, 2009, in mYsQL and tagged , , . Bookmark the permalink. 2 Comments.

  1. maksh ttrialx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: