Arsip: [HINT] Trigger dan Store Procedure In mySQL.
more 16 years ago
cyber_hecker
permintaan seseorang untuk memberikan contoh sederhana penggunaan trigger dan store procedure.
contoh trigger sederhana :
[sql:1:beafe12bb2]-- Table: tb_customer
-- DROP TABLE tb_customer;
CREATE TABLE tb_customer (
id_cust varchar(3) NOT NULL,
nm_cust varchar(50),
alamat_cust varchar(50),
updated date,
status varchar(10),
/ Keys /
PRIMARY KEY (id_cust)
) ENGINE = InnoDB;
CREATE UNIQUE INDEX id_cust
ON tb_customer
(id_cust);
DELIMITER |
CREATE DEFINER = 'root'@'localhost' TRIGGER trig_inserting
BEFORE INSERT
ON tb_customer
FOR EACH ROW
BEGIN
/ Trigger text /
SET New.updated = NOW();
SET NEW.status = 'BARU';
END|
DELIMITER ;
DELIMITER |
CREATE DEFINER = 'root'@'localhost' TRIGGER trig_updating
BEFORE UPDATE
ON tb_customer
FOR EACH ROW
BEGIN
/ Trigger text /
SET NEW.updated = NOW();
SET NEW.status = 'UPDATE';
END|
DELIMITER ;[/sql:1:beafe12bb2]
trigger ini berfungsi untuk update nilai yang baru dimasukkan / edit pada field updated -> di isi tanggal insert / update, dan status -> status apakah update atau baru.
kelebihan trigger (menurutku) :D :
- mengurangi beban jaringan.. karena pengiriman data lebih sedikit. harusnya 5 jadi 3 data yang dikirim, 2 field di kerjakan di server.
kekurangan trigger (menurutku) :D :
- sulit melakukan pembacaan kode program. karena tanpa ada perintah kok ada field yang berubah. apa gak bingung tuh.
contoh store procedure sederhana :
[sql:1:beafe12bb2]-- Procedure: sp_ins_customer
-- DROP PROCEDURE sp_ins_customer;
DELIMITER |
CREATE PROCEDURE sp_ins_customer
(
IN pid_cust varchar(2),
IN pnm_cust varchar(50),
IN palamat_cust varchar(50)
)
COMMENT 'MEMASUKKAN NILAI KE TABEL CUSTOMER'
BEGIN
/ Procedure text /
INSERT tb_customer
(id_cust, nm_cust, alamat_cust)
VALUES
(pid_cust, pnm_cust, palamat_cust);
END|
DELIMITER ;[/sql:1:beafe12bb2]
cara pemanggilan :
CALL sp_ins_customer('04','CAPPUCINO','JL. KUBURAN 55');
more 16 years ago
mas_kofa
aku lebih suka pake fungsi "generate sql str " yang aku bangun sendiri sesuai kebutuhanku.........
selain bisa menyingkat coding...juga bisa menghindari penggunaan "trigger"..................
kekurangan cara diatas......adalah kita harus berpikir keras u/ membangun fungsi tersebut.........
more 16 years ago
onsir
CREATE PROCEDURE sp_ins_customer
(
IN pid_cust varchar(2),
IN pnm_cust varchar(50),
IN palamat_cust varchar(50)
)
COMMENT 'MEMASUKKAN NILAI KE TABEL CUSTOMER'
BEGIN
/ Procedure text /
INSERT tb_customer
(id_cust, nm_cust, alamat_cust)
VALUES
(pid_cust, pnm_cust, palamat_cust);
END|
DELIMITER ;
klw cth di atas, bagaimana caranya nilai values di isi dari txtKode.Text, txtnama.Text, txtalamat.Text
apakah harus di modifikasi lagi ?
terima kasih atas penjelasanya.
more 16 years ago
saysansay
Xixixixixi Para Master Gi BerExperiment Nech..Ikutan ach....Untuk Om @Onsir PenggunaanNya Kira2 Seperti Ini Kalu Pake Zeos
ZStoredProc1.ParamByName('pid_cust').AsString=txtKode.Text more ...
- Pages:
- 1
reply |
Report Obsolete
Last Articles
Last Topic
- PascalTalk #6: (Podcast) Kuliah IT di luar negeri, susah gak sih?
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #5: UX: Research, Design and Engineer
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #4: Obrolan Ringan Seputar IT
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #2: Membuat Sendiri SMART HOME
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #3: RADically Fast and Easy Mobile Apps Development with Delphi
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #1: Pemanfaatan Artificial Intelligence di Masa Covid-19
by LuriDarmawan in Tutorial & Community Project more 3 years ago - Tempat Latihan Posting
by LuriDarmawan in OOT more 4 years ago - Archive
- Looping lagi...
by idhiel in Hal umum tentang Pascal Indonesia more 11 years ago - [ask] koneksi ke ODBC user Dsn saat runtime dengan ado
by halimanh in FireBird more 11 years ago - Validasi menggunakan data tanggal
by mas_kofa in Hal umum tentang Pascal Indonesia more 11 years ago
Random Topic
- Nampilkan Data pa TDBgrid dengan tampilan select row
by Melon_Cry in MySQL more 17 years ago - Error : "GRID INDEX OUT OF RANGE"
by flamade in Hal umum tentang Pascal Indonesia more 16 years ago - Anggota Baru
by rusli_chow in Tip n Trik Pemrograman more 16 years ago - Sederhana tapi ga tahu ngakalinya....help me?
by johnizzy in Tip n Trik Pemrograman more 15 years ago - tanya tentang penggunaan left join
by sky47 in MySQL more 14 years ago - Menggunakan RandG
by agnes in Hal umum tentang Pascal Indonesia more 16 years ago - Downtime Notice !
by LuriDarmawan in Hal umum tentang Pascal Indonesia more 14 years ago - Image Grayscale.....
by yadi in Hal umum tentang Pascal Indonesia more 16 years ago - Thread utk Ping ke Banyak IP
by rudisbg in Network, Files, I/O & System more 12 years ago - Convert File dan ekstensi
by dadanarifin in OOT more 13 years ago