Tutor Mysql Trigger function view dan stored Porcedure

Selamat siang..... :) hm..., kebetulan lagi sante, karena kerjaan sedikit berkurang, ga punya ide mau ngapain so ada baiknya berbagi pengetuan aja :), yah kali ini saya mencoba berbagi tentang penggunaan beberapa fungsi yang ada di database mysql, saya rasa sudah pada tau dong mysql itu apaan ? buat yang belom tau mysql itu merupakan sebuah (DBMS) Database management System yang gunanya untuk nyimpen data kalo mau lebih detil bisa search di google yak.. yang udah sering bikin aplikasi baik berbasis web atau engga sudah tidak asing dengan penggunaan database mysql ini, saya tidak akan membahasa tentang bagaimana menggunakan database mysql secara detil, karena sudah banyak tutorialnya berserakan di internet, tetapi lebih kepada fungsi fungsi yang kadang-kadang jarang di pakai walapun pada dasarnya sebenarnya fugnsi fungsi ini cukup membantu dalam banyak hal

TRIGGER

Trigger adalah sebuah Object Script MySQL Command yang memicu suatu kejadian dalam database MySQL berupa aksi insert, update dan delete setelah syarat tertentu. Lokasi penulisan Trigger adalah di dalam database yang bersangkutan, dan Trigger tidak ditempatkan di script Aplikasi. Hasil penulisan dari sebuah Trigger akan menimbulkan efek manipulasi database secara otomatis sesuai dengan yang kita inginkan. Misalnya, setelah insert pada tabel_a dan kolom a1 maka akan otomatis insert pada tabel_b pada kolom b1. Begitu pula untuk aksi update dan delete, Kita dapat membuat trigger dengan perintah CREATE TRIGGER. Berikut adalah syntax lengkapnya : 

Code:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

trigger_name : nama trigger
trigger_time : kapan kita mengeksekusi trigger, apakah sebelum atau sesudah perubahan pada row data table. Jadi pilihannya adalah AFTER atau BEFORE.
trigger_event : merupakan event atau peristiwa yang menyebabkan trigger dilakukan. Pilihan event tersebut adalah INSERT, UPDATE, DELETE. tbl_name : nama table.
trigger_body : statement-statement perintah SQL yang akan dilakukan. Jika perintahnya lebih dari satu maka gunakan dalam blok statement BEGIN ... END.
Jika DEFINER dispesifikasikan maka kita memutuskan trigger tersebut dijalankan hanya oleh user tertentu (dalam format penulisan user@host). Jika tidak dispesifikasikan, maka user yang melakukan perubahan (CURRENT_USER) adalah pilihan default.

Karena trigger digunakan pada saat terjadi perubahan row data, maka kita perlu referensi ke row sebelum dan sesudah perubahan. Untuk ini ada dua alias yang berfungsi untuk hal tersebut yaitu OLD dan NEW. Sesuai namanya, OLD digunakan untuk referensi sebelum perubahan dan NEW untuk referensi sesudah perubahan

Trigger After Delete

Berikut adalah contoh penggunaan trigger untuk event setelah penghapusan (AFTER DELETE) pada table "tr_penjualan" Kita akan membuat satu table audit dengan nama "tr_penjualan_hapus" yang berisi row-row yang dihapus dari table "tr_penjualan" dengan tambahan dua field, yaitu tanggal penghapusan (tgl_perubahan) dan user MySQL yang melakukan hal tersebut (nama_user).Berikut adalah perintahnya :

Code:

CREATE TABLE `tr_penjualan_hapus` LIKE `tr_penjualan`;
ALTER TABLE `tr_penjualan_hapus` ADD
    (
    `tgl_perubahan` DATETIME,
    `nama_user` VARCHAR(200)
    );

berikutnya adalah membuat trigger yang akan melakukan populasi data yang dihapus dari "tr_penjualan" ke table "tr_penjualan_hapus". Berikut adalah perintahnya :

Code:

CREATE TRIGGER hapus_tr_penjualan AFTER DELETE
    ON tr_penjualan FOR EACH ROW
BEGIN
  INSERT INTO tr_penjualan_hapus
        (       tgl_transaksi,
                kode_cabang,
                kode_kasir,
                kode_item,
                kode_produk,
                jumlah_pembelian,
                tgl_perubahan,
                nama_user
        )
  VALUES
        (       OLD.tgl_transaksi,
                OLD.kode_cabang,
                OLD.kode_kasir,
                OLD.kode_item,
                OLD.kode_produk,
                OLD.jumlah_pembelian,
                SYSDATE(),
                CURRENT_USER
        );
END;

Setelah trigger di atas kita buat, sekarang saatnya kita melakukan pengujian. Coba hapus tiga row data dari table "tr_penjualan" dan lihat efeknya di table "tr_penjualan_hapus".Jalankan perintah berikut :

Code:

DELETE FROM tr_penjualan LIMIT 3;
SELECT * FROM tr_penjualan_hapus;

Terlihat pada gambar di atas 3 row yang dihapus telah "pindah" ke table "tr_penjualan_hapus" dengan tambahan informasi waktu penghapusan dan user yang menghapus.

View
View adalah perintah query yang disimpan pada database dengan suatu nama tertentu, sehingga bisa digunakan setiap saat untuk melihat data tanpa menuliskan ulang query tersebut. sebagian orang yang bilang kalau view adalah tabel temporari kadang ada juga yang bilang virtual tabel, Syntax dasar perintah untuk membuat view adalah sebagai berikut :

Code:

DROP VIEW IF EXISTS `siemas`.`contoh_view`;
CREATE OR REPLACE
    VIEW `siemas`.`contoh_view`
    AS
        (SELECT * FROM ...)

Kita menggunakan opsi OR REPLACE jika kita ingin mengganti view dengan nama yang sama dengan perintah tersebut. Jika tidak maka perintah CREATE VIEW akan menghasilkan error jika nama view yang ingin dibuat sudah ada sebelumnya. Kita akan membuat view dari laporan deposit sebuah rumah sakit (Query yang digunakan sama dengan query pada select)". Perintahnya adalah sebagai berikut :

Code:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `lap_deposit` AS
select `t_admission`.`masukrs`
    AS `masukrs`,`t_admission`.`deposit`
    AS `deposit`,`t_deposit`.`TANGGAL`
    AS `TANGGAL`,`t_admission`.`keluarrs`
    AS `keluarrs`,`m_pasien`.`NAMA`
    AS `NAMA`,`t_admission`.`nomr`
    AS `nomr`
from ((`t_admission`
    join `t_deposit`
        on(((`t_admission`.`nomr` = `t_deposit`.`NOMR`)
    and (`t_admission`.`id_admission` = `t_deposit`.`IDADMISSION`))))
    join `m_pasien`
        on((`t_admission`.`nomr` = `m_pasien`.`NOMR`)))
group by `t_admission`.`id_admission`

Setelah View dibuat maka view dapat dipanggil layaknya melakukan query pada query normal, seperti gambar



View Dengan Paramter

Jika pembuatan View secara normal mungkin teman-teman sudah paham maksudnya, tetapi bagaiama dengan penggunaan view yang menggunakan parameter seperti layaknya stored procedure ? nanti kita akan membahas stored procedure, tetapi secara singkat pemanggilan stored procedure adalah seperti memanggil sebuah fungsi dalam program misalnya seperti CALL nama_stored_procedure() tetapi tidak dengan view, view dianggil dengan perintah select seperti halnya perintah query biasa, kali ini kita akan mencoba membuat sebuah view yang menggunakan parameter

Kasusnya simple, misalnya saya ingin menampilkan laporan harian absensi pegawai perbulan selama 30 hari, tentu saja jika menggunakan query standar atau view maka perintahnya akan seperti berikut

Code:

DROP VIEW IF EXISTS `siemas`.`rekap`;
CREATE
    VIEW `siemas`.`rekap`
    AS
    SELECT id_pegawai as kode,pegawai.nama as NAMA,
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=2011 and MONTH(tanggal)=8 AND DAY(tanggal)=1) as '1',
    .....
    .....
    .....
    FROM pegawai group by NAMA

hasil view dia atas tidaklah salah, karena data akan muncul dengan baik, permasalahanya adalah ketika kita akan menampilkan data dengan berbagai parameter, misalnya tahun 2013 dan bulan April sebanyak 30 hari, tentu saja akan membutuhkan waktu yang lama, untuk itu kita akan membuat view yang menggunakan parameter, langkah pertama yang harus kita lakukan adalah menbuat dua buah fungsi dalam mysql sebagai parameter view, caraynya sebagai berikut

Code:

create function tahun() returns INTEGER DETERMINISTIC NO SQL return @tahun;
create function bulan() returns INTEGER DETERMINISTIC NO SQL return @bulan;

Setelah Function kita buat slanjutnya kita merubah view yang sudah ada, dan menggantikan tahun dan bulan sebagai parameter dalam view, srciptnya seperti berikut

Code:

DROP VIEW IF EXISTS `siemas`.`rekap`;
CREATE
    VIEW `siemas`.`rekap`
    AS
    SELECT id_pegawai as kode,pegawai.nama as NAMA,
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=1) as '1',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=2) as '2',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=3) as '3',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=4) as '4',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=5) as '5',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=6) as '6',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=7) as '7',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=8) as '8',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=9) as '9',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=10) as '10',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=11) as '11',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=12) as '12',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=13) as '13',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=14) as '14',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=15) as '15',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=16) as '16',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=17) as '17',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=18) as '18',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=19) as '19',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=20) as '20',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=21) as '21',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=22) as '22',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=23) as '23',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=24) as '24',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=25) as '25',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=26) as '26',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=27) as '27',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=28) as '28',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=29) as '29',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=30) as '30',
    (select CASE hadir when 0 then 'HADIR' ELSE 'TIDAK' END from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND DAY(tanggal)=31) as '31',
    (select SUM(hadir) from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan() AND hadir IS NOT NULL) as 'TOTAL KEHADIRAN',
    (select FORMAT(AVG(hadir),2) from absensi where id_pegawai=kode and YEAR(tanggal)=tahun() and MONTH(tanggal)=bulan()) as 'PRESENTASE'
    FROM pegawai group by NAMA

cara pemanggilan view akan sedikit berbeda dari pemanggilan view secara normal, tetapi masih tetap menggunakan perintah select seperti biasanya, berikut cara pemnaggilan view yang menggunakan parameter

Code:

select s.* from (select @tahun:=2011 p, @bulan:=08 b) parm , rekap s;

Perintah query view diatas sebenarnya dapat di persingkat dengan menggunakan query pivot pada mysql, nanti jika ada ada waktu saya akan mencoba membahasanya,berikut hasil dari perintah jika view menggunakan parameter dijalankan



stored Procedure

Stored procedure adalah salah satu objek routine yang tersimpan pada database MySQL dan dapat digunakan untuk menggantikan berbagai kumpulan perintah yang sering kita gunakan, seperti misalkan sejumlah row ke table lain dengan filter tertentu. Stored procedure sangat berguna ketika kita tidak ingin user mengakses table secara langsung, atau dengan kata lain membatasi hak akses user dan mencatat operasi yang dilakukan. Dengan demikian resiko kebocoran dan kerusakan data dapat lebih diminalisir. apa yang membuat store procedure menjadi menarik ? Apa sih keuntungan-keuntungan penggunaan store procedure ini, setidaknya ada beberapa hal yang bisa dicatat :

Menghilangkan kemubadziran. Seperti sudah saya ungapkan di atas. Oracle, Postgree dan tentu saja MySQL (versi 5.0 ke atas) sudah mendukung adanya Store Procedure, sungguh sayang jika kita lewatkan begitu saja. Kan yo kasihan pembuatnya sudah susah payah menyediakan fitur, jika tidak dimanfaatkan. Ketika menggunakan multi koneksi database yang berbeda-beda, maka dengan store procedure akan memberikan performa yang sama. Tentu saja lebih aman. Karena programmer aplikasi tidak perlu mengetahui daftar tabel, dsb. Programmer aplikasi hanya memakai procedure yang telah disiapkan oleh Database Engineer, Meningkatkan performa. Karena akan meminimalkan komunikasi antara server (database) dengan client (aplikasi PHP). Mungkin saja masih ada atau mungkin malah banyak keuntungan lainnya. Tetapi daftar di atas bagi saya sudah cukup alasan untuk mengeksplore dan menggunakan store procedure di aplikasi yang dibangun untuk ke depannya, Kita dapat membuat trigger dengan perintah CREATE PROCEDURE. Berikut adalah syntax lengkapnya :

Code:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name (proc_parameter[,...])
    [characteristic ...] routine_body

sp_name : nama stored procedure. proc_parameter : parameter input / output dari stored procedure tersebut (opsional). characteristic : menjelaskan karakteristik dari stored procedure (COMMENT, LANGUAGE SQL, dan lain-lain). routine_body : kumpulan perintah pada stored procedure tersebut. Jika DEFINER dispesifikasikan maka kita memutuskan trigger tersebut dijalankan hanya oleh user tertentu (dalam format penulisan user@host). Jika tidak dispesifikasikan, maka user yang melakukan perubahan (CURRENT_USER) adalah pilihan default.

Berikut adalah contoh pembuatan dan penggunaan stored procedure untuk menghapus data berdasarkan "kode produk" untuk tiga table yaitu table "ms_produk", "ms_harga_harian", dan "tr_penjualan". Buatlah satu stored procedure dengan nama HapusProduk, dengan satu argumen yaitu kode_produk_param bertipe teks (varchar) dengan perintah berikut :

Code:

CREATE PROCEDURE HapusProduk(IN kode_produk_param VARCHAR(12))
BEGIN
        DELETE FROM ms_produk WHERE kode_produk = kode_produk_param;
        DELETE FROM ms_harga_harian WHERE kode_produk = kode_produk_param;
        DELETE FROM tr_penjualan WHERE kode_produk = kode_produk_param;
END

Sebelum kita mengeksekusi stored procedure tersebut. Kita coba lihat hasil query untuk produk "PROD-0000002" untuk ketiga table yang disebutkan di atas. Ini untuk memastikan adanya row untuk produk tersebut.



Sekarang coba panggil stored procedure HapusProduk dengan parameter "PROD-0000002" dan tunggu beberapa saat sampai eksekusi selesai.

Code:

CALL HapusProduk('PROD-0000002');

Coba lakukan kembali perintah pada point no. 3 dan terlihat bahwa sudah tidak terdapat data untuk produk "PROD-0000002" pada ketiga table tersebut.



Segitu dulu dari saya, semoga bermanfaat untuk teman-teman, thanks for reading

Code:

sumber
http://shofiqsula.wordpress.com/2010/02/10/procedure-dalam-mysql/
http://mysql.phi-integration.com/sql/membuat-stored-procedure-di-mysql
http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx
http://devilzc0de.org/forum/thread-19528.html