Fungsi trigger di MySQL mampu dibuat untuk melakukan pengecekan terhadap data nomor handphone yang dientri, lalu mendapatkan nama operator yang digunakan.
Query Trigger MySQL :
DROP TRIGGER IF EXISTS trigger_update_operator;
DELIMITER $$
CREATE TRIGGER trigger_update_operator
BEFORE INSERT ON `tabel_pelanggan`
FOR EACH ROW
BEGIN
DECLARE inisial_hp VARCHAR(255);
DECLARE new_operator VARCHAR(255);
IF(LEFT(new.phone,1)='0') THEN
SET inisial_hp:=LEFT(new.phone,4);
ELSEIF(LEFT(new.phone,3)='+62') THEN
SET inisial_hp:=LEFT(REPLACE(new.phone,'+62','0'),4);
ELSEIF(LEFT(new.phone,2)='62') THEN
SET inisial_hp:=LEFT(REPLACE(new.phone,'62','0'),4);
END IF;
IF(INSTR(',0811,0812,0813,0821,0822',inisial_hp)>0) THEN
SET new_operator='Simpati';
ELSEIF(INSTR(',0823,0851,0852,0853',inisial_hp)>0) THEN
SET new_operator='Kartu AS';
ELSEIF(INSTR(',0831,0832,0833,0838',inisial_hp)>0) THEN
SET new_operator='Axis';
ELSEIF(INSTR(',0814,0815,0816,0855,0858',inisial_hp)>0) THEN
SET new_operator='Mentari';
ELSEIF(INSTR(',0817,0818,0819,0859,0877,0878,0879',inisial_hp)>0) THEN
SET new_operator='XL';
ELSEIF(INSTR(',0856,0857',inisial_hp)>0) THEN
SET new_operator='IM3';
ELSEIF(INSTR(',0881,0882,0883,0884,0885,0886,0887,0888,0889',inisial_hp)>0) THEN
SET new_operator='SmartFren';
ELSEIF(INSTR(',0894,0895,0896,0897,0898,0899',inisial_hp)>0) THEN
SET new_operator='Tri';
ELSE
SET new_operator='Other';
END IF;
SET new.operator = new_operator;
END$$
DELIMITER;
Penjelasan Sistem Kerja Query SQL :
- Menghapus apabila sudah terdapat trigger dengan nama yg sama sebelumnya (trigger_update_operator)
- Menyeragamkan inisial nomor selular (081,+6281,6281) menjadi 081
- Mengecek kelompok operator pada nomor selular
- Mengupdate data operator pada tabel yang dituju (pada contoh : tabel_pelanggan)
Bayu Kandukeswara