View, Store procedure dan Triger dalam MySql

TRIGGER

Menurut Wikipedia, trigger dalam database diartikan sebagai “procedural code that is automatically executed in response to certain events on a particular table or view in a database“. Singkatnya,trigger merupakan sekumpulan perintah atau sintaks yang akan secara otomatis dijalankan jika terjadi operasi tertentu dalam tabel atau view. Trigger digunakan untuk memanggil satu atau beberapa perintah SQL secara otomatis sebelum atau sesudah terjadi proses INSERT, UPDATE atau DELETE dari suatu tabel. Sebagai contoh misalnya kita ingin menyimpan id pelanggan secara otomatis ke tabel ‘log’sebelum menghapus data di tabel pelanggan.

Di MySQL, Triggers mulai dikenal di versi MySQL 5.0, dan di versi saat ini (5.1.4) fungsionalitasnya sudah bertambah. Pada versi selanjutnya pihak pengembang MySQL berjanji akan lebih menguatkan (menambah) fitur trigger ini.

Trigger sering digunakan, antara lain untuk:

·         Melakukan update data otomatis jika terjadi perubahan. Contohnya adalah dalam sistem penjualan, jika dientri barang baru maka stock akan bertambah secara otomatis.

·         Trigger dapat digunakan untuk mengimplementasikan suatu sistem log. Setiap terjadi perubahan, secara otomatis akan menyimpan ke tabel log.

·         Trigger dapat digunakan untuk melakukan validasi dan verifikasi data sebelum data tersebut disimpan.

Berikut ini bentuk umum perintah untuk membuat triggers:

CREATE TRIGGER name

[BEFORE|AFTER] [INSERT|UPDATE|DELETE]

ON tablename

FOR EACH ROW statement

Keterangan dari bentuk umum perintah membuat trigger:

·         name, Nama trigger mengikuti peraturan penamaan variabel / identifier dalam MySQL

·         [BEFORE | AFTER] digunakan untuk menentukan kapan proses secara otomatis akan dieksekusi, sebelum atau sesudah proses.

·         [INSERT | UPDATE | DELETE] digunakan untuk menentukan event (proses) yang dijadikan trigger (pemicu) untuk menjalankan perintah-perintah di dalam triggers.

·         tablename, merupakan nama tabel dimana trigger berada.

·         statement, merupakan sekumpulan perintah atau query yang akan secara otomatis dijalankan jika event / proses yang didefinisikan sebelumnya aktif.

DELIMITER $$

CREATE TRIGGER penjualan.before_insert BEFORE INSERT ON penjualan.pelanggan

FOR EACH ROW BEGIN

INSERT INTO `log` (description, `datetime`, user_id)

VALUES (CONCAT('Insert data ke tabel pelanggan id_plg = ', NEW.id_pelanggan), now(), user());

END;

$$ 

DELIMITER ;

Untuk menghapus trigger, dapat menggunakan perintah DROP TRIGGER dengan diikuti dengan nama tabel dan nama triggernya. Berikut ini bentuk umum dan contoh perintah untuk menghapus trigger.

Bentuk umum dan contoh menghapus trigger:

DROP TRIGGER tablename.triggername;

Contoh menghapus trigger bernama ‘before_insert’ yang ada di tabel pelanggan.

DROP TRIGGER penjualan.before_insert;

VIEW

SQL View pada MySQL digunakan untuk membuat tampilan sebuah tabel. Di dalamnya memungkinkan kita untuk bisa membuat, mengupdate dan menghapus tampilan tabel tersebut. Dan tabel yang ditampilkan merupakan tabel hasil dari perintah-perintah MySQL.

Didalamnya akan terlihat adanya baris dan kolom seperti halnya tabel biasa dari sebuah database. Dan tabel yang ditampilkan hanya satu tabel sekalipun merupakan hasil perintah join pada MySQL.

Untuk membuat View kita menggunakan perintah CREATE VIEW dengan bentuk sbb:

CREATE VIEW namaview AS
SELECT namakolom
FROM namatabel
WHERE persyaratan

View yang dihasilkan merupakan data yang terupdate karena setiap kita menjalakan kueri CREATE VIEW tersebut, maka database akan mengcreate data terbaru. Contoh:

CREATE VIEW Mahasiswa Jawa Barat AS
SELECT ID,Nama
FROM Mahasiswa
WHERE Propinsi=Jawa Barat

Hal ini akan menghasilkan tampilan data ID dan nama mahasiswa yang berasal dari propinsi Jawa Barat.

Sedangkan untuk menghapus sebuah view kita menggunakan perintah DROP pada kueri MySQL. Perintahnya adalah sbb:

DROP VIEW Mahasiswa Jawa Barat

Maka view tersebut akan terhapus.

Stored Procedure

Stored Procedure adalah kumpulan perintah SQL yang diberi nama dan disimpan di server. Stored Procedure biasanya berisi perintah-perintah umum yang berhubungan dengan database pada server, dan menghasilkan sekumpulan record. Stored Procedure ditulis dalam bentuk sebuah script.

Stored Procedure akan meningkatkan performasi database, meningkatkan sekuritas database. Performasi ditingkatkan melalui pemakaian tempat penyimpanan lokal (lokal terhadap database), program yang telah terkompilasi terlebih dahulu, dan pemakaian cache. Setiap kali sebuah perintah atau Stored Procedure dikirimkan ke server untuk diproses, server harus menentukan apakah dikirimkan ke server untuk diproses, server harus menentukan apakah pengirim mempunyai hak untuk menjalankan perintah tersebut dan apakah perintah tersebut benar, Setelah hak dan perintah diperiksa dan benar, SQL Server akan membuat rencana eksekusi.

Tabel Sales Order
Sebagai contoh kita akan membuat tabel sales order dan diisi dengan sebuah data:

CREATE TABLE sales_order(
customer_name VARCHAR( 50 ) NOT NULL ,
total_price DECIMAL( 9, 0 ) NOT NULL DEFAULT '0',
total_hpp DECIMAL( 9, 0 ) NOT NULL DEFAULT '0',
profit DECIMAL( 9, 0 ) NOT NULL DEFAULT '0',
so_id INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (so_id)
) ENGINE = InnoDB;
INSERT INTO sales_order (customer_name,so_id) VALUES ('Serba Prima', 1);

Tabel Sales Item
Kemudian kita akan membuat tabel sales_item diisi dengan 2 data:

CREATE TABLE sales_item(
so_id INT NOT NULL ,
item_name VARCHAR( 100 ) NOT NULL ,
item_price DECIMAL( 8, 0 ) NOT NULL ,
hpp DECIMAL( 8, 0 ) NOT NULL ,
item_id INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (item_id)
) ENGINE = InnoDB;

INSERT INTO sales_item(so_id,item_name,item_price,hpp) VALUES (1, 'Keyboard', 100000,85000);
INSERT INTO sales_item(so_id,item_name,item_price,hpp) VALUES (1, 'Mouse', 50000,40000);

Store Procedure Resume Sales Order
Kemudian Store Procedurenya
CREATE PROCEDURE resume_sales_order (IN p_so_id INT)
BEGIN
    DECLARE v_total_price DECIMAL;
    DECLARE v_total_hpp DECIMAL;
    SELECT SUM(item_price),SUM(hpp) INTO v_total_price,v_total_hpp FROM sales_item WHERE so_id=p_so_id;
    UPDATE sales_order SET total_price=v_total_price,total_hpp=v_total_hpp,profit=v_total_price-v_total_hpp WHERE so_id=p_so_id;
END
//

Menjalankan Store Procedure
Untuk menjalankan store procedurnya dengan menjalankan 
CALL resume_sales_order(1)