Implementing Trigger pada SQL Server

sebelum qta masuk pembahasan mengenai apa itu trigger, yuk qta kenalan sama si STORED PROCEDURE terlebih dahulu. kenapa? karna trigger merupakan bentuk khusus dari yang namanya stored procedure. apa sih itu stored procedure? oke yuk disimak sedikit penjelasan mengenai hal tersebut. 

STORED PROCEDURE

Store procedure adalah kumpulan pernyataan T-SQL dan bahasa kontrol-of-flow yang disimpan di bawah satu nama, dan dijalankan sebagai satu kesatuan. Stored Procedure lebih efisien dari perintah biasa, karena procedure disimpan di server pada saat dibuat dan dijalankan di server. Pembuatan store procedure dapat dilakukan dengan menggunakan statement CREATE PROCEDURE. 

Syntax untuk membuat procedure adalah sebagai berikut:

CREATE PROCEDURE proc_nameASBEGIN            sql_statement1            sql_statement2END

untuk memanggil suatu stored procedure yang ada, bisa dipanggil dengan menggunakan perintah berikut:

EXECUTE proc_name  or
  EXEC proc_name
  or  proc_name

nah kalo udah dibuat tuh stored procedure, selanjutnya qta bisa melihat stored procedure yang pernah dibuat dengan menggunakan perintah berikut:

 sp_helptext proc_name 

Contoh:

buat database dan table terlebih dahulu untuk dapat mempraktekan pembuatan stored procedurenya. 

• database: dbpenjualan

           CREATE DATABASE dbpenjualan

• tabel: Shopper,tabel Toys dan tabel ShoppingCart

CREATE TABLE Shopper(

cShopperId char(6) NOT NULL, 

cPassword char(10) NOT NULL, 

vFirstName varchar(20) NOT NULL, 

vLastName varchar(20) NOT NULL, 

vEmailId varchar(40) NOT NULL, 

vAddress varchar(40) NOT NULL, 

cCity char(15) NOT NULL, 

cState char(15) NOT NULL, 

cCountryId char(3) NULL, 

cZipCode char(10) NULL, 

cPhone char(15) NOT NULL, 

cCreditCardNo char(16) NOT NULL,

vCreditCardType varchar(15) NOT NULL, 

dExpiryDate datetime NULL)

CREATE TABLE Toys(

cToyId char(6) NOT NULL Primary Key,

vToyName varchar(20) NOT NULL,

vToyDescription varchar(250) NULL,

cCategoryId char(3) NULL,

mToyRate money NOT NULL,

cBrandId char(3) NULL,

imPhoto image NULL,

siToyQoh smallint NOT NULL,

siLowerAge smallint NOT NULL,

siUpperAge smallint NOT NULL,

siToyWeight smallint NULL)

CREATE TABLE ShoppingCart(

cCartId char(6) NOT NULL Primary Key,

cToyId char(6) NOT NULL,

siQty smallint NOT NULL)

Supaya lebih real, sebelum masuk ke stored procedure isi dulu ya tabelnya. silahkan di isi sembarang dengan data sesuai tipedata yang telah ditetapkan.

jika database dan tablenya sudah ready, nah coba sekarang qta praktek membuat stored procedurenya.

misalkan qta sering membutuhkan laporan tentang data shopper yang hanya menampilkan nama depan, nama belakang dan e-mail. nah daripada setiap mau print laporan harus memasukan query, lebih baik dibuatkan sebuah stored procedure sehingga, sewaktu2 dibutuhkan tinggal memanggil saja. stored procedure yang dibutuhkan adalah sebagai berikut:

CREATE PROCEDURE prcDisplayShopperAs SELECT vFirstName, vLastName, vEmailId  FROM Shopper

pada saat laporan tersebut dibutuhkan, tinggal dipanggil dengan perintah berikut:

EXEC prcDisplayShopper

maka akan ditampilkan data tentang shopper sesuai dengan yang diminta.

INPUT PARAMETER PADA STORED PROCEDURE

Parameter adalah penampung dalam query atau stored procedure yang menerima nilai yang ditetapkan pengguna kapanpun query atau store procedure  akan dieksekusi. Parameter dideklarasikan dalam bagian deklarasi parameter berfungsi sebagai sarana untuk pertukaran data antara store procedure dan object yang dipanggil tersebut.

misalkan dari data Toys, dibutuhkan data tentang harga dan nama toys berdasarkan IdToys yang diinput oleh user. 
maka stored procedure yang dapat digunakan adalah sebagai berikut:

CREATE PROCEDURE prcDisplayPrice @ToyId char(6)ASSELECT vToyName,mToyRateFROM ToysWHERE cToyId=@ToyId 

untuk mengecek stored procedure tersebut lakukan pemanggilan sebagai berikut:

exec prcDisplayPrice '000011' 

Jika ID "000011" datanya ada pada tabel Toys, maka data nama dan harga nya akan tampil. jika data tersebut tidak ada, maka hanya akan menampilkan nama kolomnya saja.

TRIGGER

Trigger adalah blok kode yang merupakan satu set pernyataan T-SQL yang aktif dalam menanggapi tindakan tertentu. Perintah-perintah yang akan mengaktifkan trigger adalah : 
 - INSERT
 - UPDATE
 - DELETE. 
Trigger dipakai untuk menjaga integritas data dan mengimplementasikan aturan bisnis yang kompleks. Trigger dibuat dengan memakai bahasa T-SQL atau SQL Enterprise Manager.

Trigger memiliki Karakteristik sebagai berikut:

• Trigger dijalankan secara otomatis oleh SQL Server ketika pernyataan modifikasi data dikeluarkan
• Trigger tidak dapat secara eksplisit dipanggil atau dijalankan, seperti dalam kasus prosedur yang tersimpan
• Trigger mencegah kesalahan, perubahan tidak sah atau tidak konsisten dalam data
• Trigger tidak bisa mengembalikan data ke pengguna

untuk membuat trigger, perintah yang dapat digunakan adalah sebagai berikut:

            CREATE TRIGGER trigger_name

            ON table_name

            [WITH ENCRYPTION]

            FOR [INSERT | DELETE | UPDATE]

            AS sql_statements 

Keterangan:
inti dari trigger harus disebutkan trigger tersebut punyanya tabel yang mana (ON), dan akan diaktifkan pada perintah DML yang mana (FOR) apakah Insert, update atau Delete.

Untuk mengecek trigger yang ada pada suatu basis data dapat dilakukan dengan perintah berikut:

sp_help nama_trigger

penggunaan trigger pada pernyataan INSERT, UPDATE maupun DELETE akan secara otomatis membuat dua table maya yaitu table INSERTED dan DELETED untuk mendeteksi modifikasi data. Table Inserted berisi salinan dari semua record yang dimasukkan dalam table trigger. Sedangkan table Deleted berisi semua record yang telah dihapus dari table trigger.

Contoh penggunaan Trigger

pada saat terjadi transaksi pembelian (insert data pada table shopping cart), maka pada perintah INSERT tersebut lakukan pengecekan terhadap qty dari toys yang akan dibeli. jika qty yang dibeli lebih besar dari stock yang ada, maka tampilkan pesan "Jumlah Beli Melebihi Jumlah Stock yang Ada. Transaksi tidakdapat di proses!" dan data batal di insert. tapi jika qty diatas batas minimal yang diberikan maka insert data ke tabel shopping cart, kurangi qty pada tabel toys sesuai transaksi pembelian dan tampilkan pesan "Data berhasil ditambahkan ke keranjang belanja Anda"

Jawab:

untuk membuat validasi diatas, maka dapat digunakan trigger insert berikut:

Insert trigger trgInsShopCart

on ShoppingCart 

For Insert 

As 

declare @qty int 

declare @jmlBeli int 

declare @idtoys char(6)  

select @idtoys = inserted.cToyId from inserted 

select @qty = siToyQoh from toys join Inserted on toys.cToyId = Inserted.cToyId 

select @jmlbeli = inserted.siQty from inserted 

if(@jmlBeli > @qty) 

   begin  

        PRINT 'Jumlah Beli Melebihi Jumlah Stock yang Ada. Transaksi tidakdapat di proses!' 

        ROLLBACK TRANSACTION 

        return 

   end 

else 

   begin 

    update toys set siToyQoh = siToyQoh - @jmlBeli where cToyId = @idtoys 

    print 'Data berhasil ditambahkan ke keranjang belanja Anda' 

   end 

Nah, cukup mudah bukan? :D itulah sekilas pengenalan Triggers yang merupakan bentuk khususon dari Stored Procedure. smoga bermanfaat.... :)