Saturday, 16 February 2019

simple mysql trigger

Category : MySql
sekarang saya mencoba untuk memberikan sedikit tutorial dasar tentang mysql trigger. trigger itu gunanya sangat banyak sekali, kita langsung ke contoh ok?

misalnya kita punya table seperti ini :
Table_blog Table_kategori
- id_blog (PK)
- id_kategori (FK)
- judul_blog
- isi_blog
- id_kategori (PF)
- nama_kategori

di table di atas terlihat ada hubungan one to many antara table_blog dengan table_kategori. dimana 1 blog hanya memiliki 1 kategori. tapi 1 kategori bisa di miliki banyak blog.

mari kita berikan sedikit data pada tabel di atas

Table_blog
id_blog id_kategori judul_blog isi_blog
 1  1 dasar  php
 ini adalah isi blog "dasar php"
 2  1 php lanjutan  ini adalah isi blog "php lanjutan"
 3  2 simple javascript
 ini adalah isi blog "simple javascript"

table_kategori
id_kategori nama_kategori
1 PHP Script
2 Javascript


pada kasus ini jadi bila kita menghilangkan kategori "php script" maka klo dalam sisi programing kita harus query lagi untuk menghapus dalam table_blog. nah sebenernya ada cara laen, yaitu mengunakan trigger dari mysql. sintaknya untuk menyelesaikan permasalahan di atas adalah :

DELIMITER $$

CREATE TRIGGER table_kategori_del AFTER DELETE on table_kategori   
FOR EACH ROW BEGIN
DELETE FROM table_blog WHERE id_kategori = OLD.id_kategori;
END$$

DELIMITER ;

perintah di atas memberitahukan mysql setelah ada penghapusan (AFTER DELETE) pada table_kategori lakukan penghapusan blog dengan kategori yang telah di hapus.

dalam pembahasan kali ini hanya membahas penghapusan, tapi ada template trigger yang bisa di pake sebagai referensi :

DELIMITER $$

DROP TRIGGER `trigger_name`$$

CREATE TRIGGER `trigger_name` BEFORE/AFTER INSERT/UPDATE/DELETE on `table_name`
FOR EACH ROW BEGIN
  INSERT INTO test2 SET a2 = NEW.a1;
  DELETE FROM test3 WHERE a3 = NEW.a1;
  UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;END$$
DELIMITER ;

semoga berguna

Like this articles? share it with Share With FacebookFacebook
<< Back
3 Comment | 7503 hits | Posted by Callrid at 2007-09-18 11:20:20

Related Read

Add Comment
Name :
Web :
Comment :

Code :
 

Other Source
A Practical Guide On Complete Multivitamins
Many of us have a hard time deciding what the best multivitamin is for us. This is completely confusing and difficult especially when there are so many bottles to scan in the various aisles of the...

High Blood Pressure
The nutritional benefits of low-fat dairy products are well known for ages. The latest studies show that the ingredients of dairy products - some combination of milk proteins are effective and...

Welcome To Mimicwatch.com - Quality Luxury Replica Vacheron Constantin Watches At Affordable Price
When entering our online store at Mimicwatch.com, you are given an unique opportunity to choose among a wide selection of famous Replica Watches and Replica handbags such as Replica Vacheron...

Ebook Marketing Tips
A great way to have a product to sell on the Internet is an e-book which almost anyone can create. There is no longer any need for delaying with the simple process detailed below. If you are afraid...

Tactical Asset Marketing
Asset Management Different Asset Allocation Strategies Asset marketing is the process of dividing a portfolio into major asset categories such as cash, stocks, real estate, or...

Shout Box
2016-11-24 08:56:05 
Pembicara Internet M
Website yagn bagus
2016-11-24 08:40:45 
Produsen Mukena
Good Website
2016-04-05 20:44:45 
Belanja Alat ukur
Belanja Alat ukur dan Thermal Camera Hanya di dutapersada.co.id
2016-04-04 07:10:03 
hit obat nyamuk
hit obat nyamuk
2016-03-31 15:26:29 
vay dam Lury 2016
We stumbled over here from a different web address and thought I might as well check things out. I like what I see so i am just following you. Look forward to finding out about your web page yet again.

Site Partners


Search Articles

Web Statistic
No Visitor :
3231811
Today Visitor :
139
Online Visitor :
1
what is my ip address? Page Rank Checker

< Feb 2019 >
SMTWTFS
     12
3456789
10111213141516
17181920212223
2425262728

Increase Page Rank

Last Modified 08-November-2011, 23:21:04 pm
callrid.com Created 2006-2012 by Anton Ongsono
All rights reserved