WhiteVinix Blog's: Stored Procedure, Fuction and Trigger

Tuesday, September 27, 2011

Stored Procedure, Fuction and Trigger

3:13 PM |

Posted by WhiteVinix |

Edit Post

STORED PROCEDURE

Pengertian 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.

Stored Procedure lebih efisien dari perintah biasa, karena procedure disimpan si server pada saat dibuat dan dijalankan di server. Stored Procedure yang berisi sekumpulan perintah Transact-SQL akan dipanggil oleh sebuah perintah Transact-SQL.

Sebelum Stored Procedure dibuat, sintaks penulisan perintah dicek kebenarannya. Jika tidak Error, nama procedure disimpan di tabel SysObjects dan teksnya disimpan di tabel SysComments. Pertama kali Stored Procedure dijalankan, rencana eksekusi dibuat dan Stored Procedure dikompilasi. Pemrosesan berikutnya akan lebih cepat, karena SQL Server tidak perlu mengecek sintaks dan membuat rencana eksekusi atau mengkompilasi procedure.

Sekali Stored Procedure dibuat, Anda dapat memanggilnya pada saat diperlukan. Cara ini meningkatkan modularitas dan memungkinkan pemakaian ulang sebuah program. Pemakaian ulang program akan memudahkan pemeliharaan sebuah database. Jika aturan bisnis berubah, Anda hanya perlu mengubah Stored Procedure, sedangkan pemanggilannya tetap.

Stored Procedure dapat menerima parameter, dan menghasilkan parameter, memberikan umpan balik dalam bentuk kode status dan teks, dan dapat memanggil procedure lain. Stored Procedure menjamin sekuritas dengan isolasi dan enkripsi. User diberi hak untuk menjalankan Stored Procedure, tetapi tidak mempunyai hak untuk mengakses database secara langsung. Dengan enkripsi, user tidak dapat membaca perintah-perintah yang dipakai dalam Stored Procedure.

Pembahasan tentang Stored Procedure :

1. Adanya dukungan Stored Procedure akan membuat program anda lebih ringkas dan mudah untuk dikembangkan.
Dengan stored procedure : algoritma yang lama dimasukkan ke dalam stored procedure di SQL server, disimpan di server (misal dengan nama DoProsesStock). Pada program, cukup ditulis perintah SQL : Exec DoProsesStock StartDate, EndDate . Selanjutnya, SQL Server akan mengeksekusi perintah proses yang anda inginkan. satu kali perubahan proses, akan berlaku untuk semua user yang terhubung ke database, bahkan saat program masih berjalan di tiap komputer user (program tidak perlu dimatikan dulu).

2. Stored procedure akan menyederhanakan perintah SELECT * FROM table yang memang rumit, misal untuk laporan. Perintahnya dan algoritma nya sama dengan yang nomor 1. Bayangkan bila anda harus membuat perintah SELECT dari sebuah tabel, tapi juga diikuti kondisi2 tertentu yang biasanya berhubungan dengan data dari tabel lain.

3. Stored procedure akan membantu anda dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali tabel2 pembantu.
Bagaimana bisa? Stored procedure, dapat membuat tabel temporary yang disimpan sementara di dalam memori server selama proses berlangsung, atau bisa juga selama program connect.

4. Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja. Komputer client dapat berupa pentium 233 MMX, tapi dapat melakukan serangkaian proses yang rumit dan tetap tidak lambat. Karena bukan client yang berpikir, tetapi server yang melakukan pekerjaan, jadi client hanya akan mendapatkan data jadinya saja.

Mengapa Mengunakan Strored Procedure ?

Dengan stored procedure eksekusi menjadi cepat. Tidak ada kompilasi. Peningkatan kecepatan datang dari reduksi lalu-lintas jaringan. Jika ada pekerjaan pengecekan berulang, looping, multiple statement, dikerjakan dengan pemanggilan tunggal ke prosedur yang telah disimpan ke server.

Stored procedure adalah komponen. Andaikan aplikasi kemudian ditulis dalam bahasa berbeda, tidak ada masalah, karena logika berada didalam database bukan dalam aplikasi.

Stored procedure adalah portable. Stored procedure ditulis dalam SQL, Anda bisa jalankan pada setiap platform dimana MySQL dijalankan disitu.

5. Pembuatan Stored Procedure :

anda dapat membuat stored procedure dengan program SQl Browser apa saja. bahkan cukup dengan sebuah TMemo, ADOConnection dan ADOQuery, anda sudah dapat membuat form untuk membuat stored procedure.
tapi yang paling mudah digunakan adalah Query Analizer dari MS SQL Server.

Syntax yang digunakan adalah :

CREATE PROCEDURE nama_stored_procedurenya @parameter1 tipe_data, @parameter2 2 tipe_data AS isi procedurenya.

Contoh

Statemen yang menciptakan strored procedure

CREATE PROCEDURE procedure1 /* nama */

(IN parameter1 INTEGER) /* parameter */

BEGIN /* awal blok */

DECLARE variable1 CHAR(10); /* variabel */

IF parameter1 = 17 THEN /* awal IF */

SET variable1 = 'burung'; /* assignment */

ELSE

SET variable1 = 'kelelawar'; /* assignment */

END IF; /* akhir IF */

INSERT INTO table1 VALUES (variable1); /* statement */

END /* akhir blok */

Parameter

1. Tanpa parameter

CREATE PROCEDURE p5

() ...

2. Satu parameter input

CREATE PROCEDURE p5

([IN] nama tipe-data) ...

3. Satu parameter output

CREATE PROCEDURE p5

(OUT nama tipe-data) ...

4. Satu parameter untuk input dan output

CREATE PROCEDURE p5

(INOUT nama tipe-data) ...

Contoh OUT

mysql> CREATE PROCEDURE p6 (OUT p INT)

-> SET p = -5 //

mysql> CALL p6(@y)//

mysql> SELECT @y//

+------+

| @y |

+------+

| -5 |

+------+

p adalah nama parameter output. Saat pemanggilan, variabel sesi @y dilewatkan.

Dalam bodi prosedur, parameter diisi dengan -5.

Efeknya sama dengan statemen “SET @y = -5;”.

Gabungan statemen

diapit dengan blok begin ... end

CREATE PROCEDURE p7 ()

BEGIN

SET @a = 2;

SET @b = 10;

SELECT nama, @a * jumlah FROM barang WHERE jumlah >= @b;

END; //

Variabel

dideklarasikan dengan statemen DECLARE

CREATE PROCEDURE p8 ()

BEGIN

DECLARE a INT;

DECLARE b INT;

SET a = 2;

SET b = 10;

SELECT nama, a * jumlah FROM barang WHERE jumlah >= b;

END; //

Penjelasan
1. nama stored_procedure tidak boleh sama dengan nama fungsi internal, misal CREATE PROCEDURE SUM, tidak boleh ada spasi, tapi bisa menggunakan karakter _
2. Untuk membuat stored procedure gunakan perintah CREATE, untuk mengedit gunakan ALTER , untuk menghapus gunakan DROP.

perintah CREATE, ALTER, DROP dapat digunakan juga untuk membuat TABLE, VIEW, TRIGGER, FUNCTION, misal CREATE VIEW, ALTER FUNCTION,dsb
3. SQL Server mengenali parameter/variabel karena ada tanda @, contoh : @nama_barang char(50), @tanggal datetime, dsb

4. Untuk deklarasi parameter di Stored Procedure gunakan DECLARE
contoh : DECLARE @StartDate datetime, @EndDate datetime, dst..
5. Untuk memasukkan nilai ke sebuah parameter, gunakan SET atau SELECT, contoh :
SET @nama='Itanium'
SET @Web='Klik-kanan' (perintah SET hanya bisa untuk 1 variabel saja)
SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisa digunakan untuk banyak variabel)

6. Untuk mengambil nilai dari sebuah field dari tabel ke dalam variabel dapat juga menggunakan SET / SELECT , misal : SET @nama= SELECT nama FROM user WHERE login='Itanium') selalu gunakan anda(),perintah ini valid bila data yang ditemukan hanya 1, bila lebih, maka varibale @nama tidak akan ada nilai nya.

7. Untuk mengambil data dalam jumlah banyak, misal seperti array atau StringList, gunakan temporary tabel.untuk pembahasan lebih lengkap, tunggu posting berikut nya.
Contoh pembuatan Stored Procedure. Kita akan membuat sebuah Stored procedure yang berfungsi untuk menggantikan perintah SELECT yang rumit, misal untuk laporan stok barang.

dalam contoh ini ada 3 tabel yang digunakan.

1. Barang (IDBarang, NamaBarang, IDSatuan)

2. Satuan (IDSatuan, Satuan)

3. StockBarang (Tanggal, IDBarang, SAwal,Masuk,Keluar,SAkhir)

laporan yang diminta adalah untuk menampilkan stock sesuai dengan periode tertentu (bisa per hari, bisa juga per minggu, tergantung inputan StartDate dan EndDate).

SQL untuk pembuatan Stored Procedure nya :

CREATE PROCEDURE LapStockBarang1 @StartDate varchar(10), @EndDate varchar(10) AS
DECLARE @tgl1 datetime, @tgl2 datetime

**(variabel StartDate tidak bisa langsung datetime karena perintah EXEC LapStockBarang dalam bentuk string)

CREATE TABLE #TStock (IDBarang varchar(5), SAwal real, Masuk real, Keluar real)
CREATE TABLE #TStock2 (IDBarang varchar(5), SAwal real, Masuk real, Keluar real, SAkhir real)
** buat temporary table , tanda # menandai bahwa tabel ini hanya akan ada saat proses stored procedure.

SELECT @tgl1= CONVERT(datetime,@StartDate,103),

tgl2=CONVERT(datetime,@EndDate,103)
** convert varchar(string) ke tipe datetime, gunakan perintah CONVERT(tipe,variabel,format).

ormat 103 adalah format dd/mm/yyyy

INSERT INTO #TStock (IDBarang, SAwal)

SELECT (IDBarang, SAwal)

FROM StockBarang

WHERE tanggal= @tgl1

** masukkan saldo awal pada tanggal bulan itu

INSERT INTO #TStock (IDBarang, Masuk, Keluar)

SELECT IDBarang, SUM(Masuk), SUM(Keluar)

FROM StockBarang

WHERE tanggal BETWEEN @tgl1 AND @tgl2

GROUP BY IDBarang

** masukkan JUMLAH dari masuk dan keluar

INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar)

SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar)

FROM #TStock

GROUP BY IDBarang

** sekarang gabungkan data2 nya

UPDATE #TStock2 SET SAkhir= SAwal + Masuk – Keluar

SELECT t.*, b.NamaBarang, s.Satuan

FROM #TStock2 AS t, Barang AS b, Satuan AS s

WHERE t.IDBarang=b.IDBarang

ND b.IDSatuan=s.IDSatuan

ORDER BY b.IDBarang

Function

Function adalah suatu blok PL/SQL yang memiliki konsep sama dengan procedure, hanya saja pada function terdapat pengembalian nilai (return value).

Karena function dapat mengembalikan sebuah nilai, function dapat diakses seperti layaknya sebuah variabel biasa.

Bentuk Umum :

CREATE OR REPLACE FUNCTION nama_function (parameter_1, …)

RETURN tipe_data AS

variabel_1 tipe_data;

…
BEGIN
statemen_1;
…
RETURN nilai_yang_dikembalikan;

END;

Statemen RETURN tipe_data diatas menunjukkan bahwa function akan mengembalikan nilai dengan tipe data tertentu

Statemen RETURN nilai_yang_dikembalikan berfungsi untuk mengembalikan nilai yang telah diproses dalam function

Function atau fungsi dalam perintah SQL digunakan untuk menghasilkan sebuah nilai atau output. Ada berbagai macam fungsi yang ada pada SQL.

1. Fungsi Agregasi/Agregates

Ø Fungsi yang dihasilkan dari sebuah group data.

a. SUM

Untuk menjumlah suatu kolom tertentu yang telah definisikan dalam perintah select.

Sintak :

SELECT SUM (nama_kolom) FROM nama_tabel

[WHERE = Kondisi]

Contoh :

SELECT SUM (SKS) FROM Mata_Kuliah

b. COUNT

Untuk menghitung jumlah baris dalam sebuah tabel.

Sintak :

SELECT COUNT nama_kolom FROM nama_tabel

[WHERE]

Contoh :

SELECT COUNT (*) FROM mahasiswa

SELECT COUNT (SKS) FROM Mata_Kuliah

c. AVG

Untuk menghitung nilai rata – rata dari suatu kolom tertentu

yang telah definisikan dalam perintah select.

Sintak :

SELECT AVG nama_kolom FROM nama_tabel

[WHERE]

Contoh :

SELECT AVG (SKS) FROM Mata_Kuliah

d. MIN

Untuk mengetahui nilai terkecil dari sebuah kolom tertentu

dalam perintah select.

Sintak :

SELECT MIN nama_kolom FROM nama_tabel

[WHERE]

Contoh :

SELECT MIN (SKS) FROM Mata_Kuliah

e. MAX

Untuk mengetahui nilai terbesar dari sebuah kolom tertentu dalam perintah select.

Sintak :

SELECT MAX nama_kolom FROM nama_tabel

[WHERE]

Contoh :

SELECT MAX (SKS) FROM Mata_Kuliah

2. Fungsi Waktu

a. GETDATE

Untuk menampilkan tanggal sekarang.

Contoh :

SELECT GETDATE()

b. DATEADD

Untuk memambah jumlah hari, tanggal atau tahun yang ditentukan.

Sintak :

DATEADD (datepart, jumlah, tanggal)

Contoh :

SELECT DATEADD (day, 10, getdate())

SELECT DATEADD (Month, 10, getdate())

SELECT DATEADD (Year, 10, getdate())

c. DATEDIFF

Untuk mengetahui rentan waktu (bisa hari, bulan, tahun).

Sintak :

DATEDIFF (datepart,tanggal_awal, tanggal_akhir)

Contoh :

SELECT DATEDIFF (month, '01/01/2007','01/01/2008')

d. DATENAME

Untuk menampilkan nama hari, bulan atau tahun.

Sintak :

DATENAME (datepart, tanggal)

Contoh :

SELECT DATENAME (month, getdate()) AS 'Bulan

Sekarang'

3. Fungsi Aritmatika

a. SQRT

Untuk mencari akar dari niai tertentu.

Contoh :

SELECT SQRT (100)

b. CEILING

Untuk pembulatan ke atas dari angka decimal

Contoh :

SELECT CEILING (25.25)

Keterangan : Akan menghasilkan nilai 26

c. FLOOR

Untuk pembulatan ke bawah dari angka decimal

Contoh :

SELECT FLOOR (25.25)

Keterangan : Akan menghasilkan nilai 25

4. Fungsi Karakter

a. LOWER dan UPPER

· LOWER = mengubah huruf besar ke huruf kecil

· UPPER = mengubah huruf kecil ke huruf besar

Contoh :

1. SELECT LOWER (Nama) FROM Mahasiswa

2. SELECT UPPER (Nama) FROM Mahasiswa

b. LEN

Untuk menghitung jumlah karakter.

Contoh :

SELECT Nama, LEN (Nama) FROM Mahasiswa

c. LTRIM dan RTRIM

· LTRIM = memotong karakter spasi di kiri string

· RTRIM = memotong karakter spasi di kanan string

Contoh :

SELECT LTRIM ('Sistem Basis Data')

SELECT RTRIM ('Sistem Basis Data ')

Keterangan :

Akan menghasilkan “Sistem Basis Data”

d. SUBSTRING

Mengambil beberapa karakter dari sebuah deretan string.

Contoh :

SELECT SUBSTRING ('Sistem Basis Data',8,5)

Keterangan :

Mengambil string dari karakter ke 8 sebanyak 5 karakter.

Hasilnya adalah ‘Basis’

e. LEFT dan RIGHT

[1] LEFT Mengambil beberapa karakter dari kiri

[1] RIGHT Mengambil beberapa karakter dari kakan

Contoh :

SELECT LEFT ('Sistem Basis Data',6)

Keterangan :

Mengambil string sebanyak 6 karakter dari sebelah kiri.

Hasilnya adalah ‘Sistem’

TRIGGER

Ø Trigger adalah Stored Procedure khusus yang dijalankan secara otomatis pada saat atau sesudah modifikasi data. Perintah-perintah yang akan mengaktifkan Trigger adalah UPDATE, INSERT, dan DELETE. Trigger dipakai untuk menjaga integritas data dan mengimplementasikan aturan bisnis yang kompleks. Trigger dibuat dengan memakai bahasa Transact-SQL atau SQL Enterprise Manager. Tugas-tugas manajemen Trigger meliputi mengubah, mengganti nama, menampilkan, menghapus dan membuat Trigger tidak aktif.\\

Ø Trigger adalah blok PL/SQL atau prosedur yang berhubungan dengan table, view, skema atau database yang dijalankan secara implicit pada saat terjadi sebuah event. Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE. INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

Trigger memakai dua tabel maya yaitu Inserted dan Deleted untuk mendeteksi modifikasi data. Untuk memprogram Trigger, Anda harus mendalami kedua tabel tersebut dan bahasa Transact-SQL. Untuk menjaga integritas data, Anda dapat memakai beberapa alternatif, yaitu Trigger atau Konstrain (Rule). Anda juga dapat memakai kunci utama dan kunci unik untuk mengidentifikasi baris dalam sebuah tabel secara unik. Anda juga dapat memakai nilai default dan domain untuk membatasi nilai-nilai yang diperbolehkan pada sebuah kolom. Referential Integrity dipakai untuk menjamin keabsahan hubungan antar tabel. Sebaliknya Anda memakai konstrain-konstrain tersebut sebelum memilih Trigger, karena Trigger dapat melakukan proses yang lebih rumit tetapi beban sistem lebih berat.

Pakailah Trigger untuk kasus-kasus berikut:

- Jika pemakaian cara deklaratif tidak dapat memenuhi kebutuhan yang diperlukan. Misalnya, buatlah Trigger untuk mengubah sebuah nilai numerik dalam tabel jika sebuah record di dalam tabel tersebut dihapus.
- Sebuah perubahan harus mengubah tabel-tabel lain yang terhubung. Misalnya jika data order ditambahkan, tabel lain yang menyimpan data stok akan berubah.
- Jika database didenormalisasi dan memerlukan cara otomasi untuk mengubah data redundan yang ada dibeberapa tabel.

- Jika sebuah nilai pada sebuah tabel harus divalidasi dengan data pada tabel lain.
- Jika diperlukan pesan kesalahan dan penangganan kesalahan secara khusus.

Kemampuan-kemampuan yang dimiliki Trigger:

- Trigger dapat menjaga referential integrity dengan melakukan perubahan data atau penghapusan kunci tamu (Foreign Key) di dalam database.

- Trigger dapat bekerja pada beberapa kolom dalam sebuah database bahkan pada objek di luar database. Anda juga dapat memakai Trigger pada View.

- Sebuah Trigger dapat melakukan beberapa aksi dan Trigger dapat diaktifkan oleh beberapa event. Misalnya Anda membuat sebuah Trigger yang akan diaktifkan jika terjadi proses INSERT, UPDATE atau DELETE. Dengan perintah Transact-SQL, Anda dapat mendefinisikan aturan bisnis untuk tiap event.

Selain kemampuan-kemampuan tersebut, Trigger juga mempunyai keterbatasan. Trigger tidak dapat dibuat pada sebuah tabel sistem atau temporer, meskipun perintah di dalama Trigger mengacu ke tabel sistem atau tabel temporer.

Trigger tidak dapat diaktifkan secara manual, tetapi diaktifkan secara otomatis jika terjadi event INSERT, UPDATE, dan DELETE. Dalam Trigger selalu berisi satu atau beberapa event tersebut sebelum perintah Transact-SQL didefinisikan. Tipe-tipe Trigger sesuai dengan event-nya. Misalnya Anda membuat sebuah Trigger UPDATE sehingga jika terjadi pengubahan data pada sebuah tabel, Trigger tersebut dapat diaktifkan. Sebuah Trigger dapat diaktifkan oleh beberapa event.

Pada saat terjadi INSERT atau UPDATE yang diaktifkan Trigger, Trigger menyimpan data baru atau data hasil modifikasi di dalam sebuah tabel yang bernama INSERTED. Jika terjadi DELETE yang mengaktifkan Trigger, data yang dihapus akan tersimpan ditabel yang bernama Deleted. Tabel tersebut berada dimemori dan dibaca oleh Trigger dengan perintah-perintah Transact-SQL. Kemampuan ini penting anda ketahui, karena pada umumnya Trigger membandingkan data dalam tabel Inserted dan Deleted dengan data baru sebelum proses Commit. Dengan kedua tabel tersebut, Trigger dapat melakukan proses Roll-Back.

Tipe dari trigger adalah :

· Application trigger : diaktifkan pada saat terjadi event yang berhubungan dengan sebuah aplikasi

· Database trigger : diaktifkan pada saat terjadi event yang berhubungan dengan data (seperti operasi DML) atau event yang berhubungan dengan sistem (semisal logon atau shutdown) yang terjadi pada sebuah skema atau database.

Trigger perlu dibuat pada saat :

· Membentuk sebuah aksi tertentu terhadap suatu event

· Memusatkan operasi global

Trigger tidak perlu dibuat, jika :

· Fungsionalitas yang diperlukan suatu ada pada Oracle server

· Duplikat atau sama dengan fungsi trigger yang lain.

Sintak penulisan dari database trigger, berisi komponen berikut :

1. Trigger timing :

a. Untuk tabel : BEFORE, AFTER

b. Untuk view : INSTEAD OF

2. Trigger event : INSERT, UPDATE atau DELETE

3. Nama tabel : yaitu nama tabel atau view yang berhubungan dengan trigger

4. Tipe trigger : Baris atau Pernyataan (statement)

5. klausa WHEN : untuk kondisi pembatasan

6. trigger body : bagian prosedur yang dituliskan pada trigger

Trigger timing adalah waktu kapan trigger diaktifkan. Ada tiga macam trigger timing, yaitu :

· BEFORE : trigger dijalankan sebelum DML event pada table

· AFTER : trigger dijalankan setelah DML event pada table

· INSTEAD OF : trigger dijalankan pada sebuah view.

Trigger event ada 3 kemungkinan : INSERT, UPDATE atau DELETE.

Pada saat trigger event UPDATE, kita dapat memasukkan daftar kolom untuk mengidentifikasi kolom mana yang berubah untuk mengaktifkan sebuah trigger (contoh : UPDATE OF salary ... ). Jika tidak ditentukan, maka perubahannya akan berlaku untuk semua kolom pada semua baris.

Tipe trigger ada 2 macam, yaitu :

· Statement : trigger dijalankan sekali saja pada saat terjadi sebuah event. Statement trigger juga dijalankan sekali, meskipun tidak ada satupun baris yang dipengaruhi oleh event yang terjadi.

· Row : trigger dijalankan pada setiap baris yang dipengaruhi oleh terjadinya sebuah event. Row trigger tidak dijalankan jika event dari trigger tidak berpengaruh pada satu baris pun.

Trigger body mendefinisikan tindakan yang perlu dikerjakan pada saat terjadinya event yang mengakibatkan sebuah trigger menjadi aktif.

~ Sintaks untuk pembuatan Trigger ~

CREATE TRIGGER <nama_trigger>

ON <nama_tabel>

FOR INSERT, UPDATE, DELETE

AS
<statement>
GO

Berikut ini adalah contoh Trigger data penjualan, dimana setiap ada data penjualan yang masuk ke tabel penjualan, maka di tabel lain akan terhitung penjumlahan total penjualan barang tersebut.

Sebelumnya kita buat terlebih dahulu sebuah tabel barang.

CREATE TABLE [dbo].[Table_barang](

[id] [int] NULL,

[kode_barang] [int] NOT NULL,

[nama_barang] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Total_Penjualan] [int] NULL,

CONSTRAINT [PK_Table_barang] PRIMARY KEY CLUSTERED

(
[kode_barang] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Pada tabel barang tersebut, isikan dengan data sebagaimana yang terlihat pada gambar di bawah ini :

Kemudian kita buat tabel penjualan barang (masing-masing item barang berbeda tabel). Contoh dibawah adalah mebuat tabel penjualan Baju :

CREATE TABLE [dbo].[Table_penjualan_baju](

[id] [int] NULL,

[kode_barang] [int] NULL,

[terjual] [int] NULL,

[tanggal] [datetime] NULL

) ON [PRIMARY]

Biarkan tabel penjualan baju tersebut di atas kosong.

Selanjutnya kita buat sebuah Trigger, dimana apabila tabel penjualan baju diisi dengan data penjualan baju, maka total penjualan baju (kolom : Terjual) akan muncul di tabel barang (kolom : Total_Penjualan).

CREATE TRIGGER [TotalPenjualan_Baju]

ON [dbo].[Table_penjualan_baju]

AFTER INSERT,DELETE,UPDATE

AS
BEGIN
SET NOCOUNT ON;

DECLARE @kode int

DECLARE @jual int

SELECT @kode = kode_barang, @jual = sum(terjual) FROM Table_penjualan_baju Group by Kode_barang

if @jual is not null

UPDATE Table_barang SET Total_penjualan = @jual WHERE kode_barang = @kode

END

Disini kita akan membuat sebuah Trigger dengan nama ‘TotalPenjualan_Baju’ di tabel ‘Table_penjualan_baju’. Kita buat 2 buah variabel (@kode dan @jual) dimana @kode adalah nilai kolom kode_barang hasil dari query :

SELECT @kode = kode_barang, @jual = sum(terjual) FROM Table_penjualan_baju Group by Kode_barang

dan variabel @jual, nilainya merupakan penjumlahan (sum) dari kolom ‘terjual’ pada query di atas.

Apabila variabel @jual memiliki nilai (is not null), maka akan dijalankan query berikutnya, yakni :

UPDATE Table_barang SET Total_penjualan = @jual WHERE kode_barang = @kode.

a. Apabila di tabel ‘Table_penjualan_baju’ diisi data, misal

maka kolom ‘Total_penjualan’ di tabel ‘Table_barang’ pun akan terisi.

b. Apabila di tabel ‘Table_penjualan_baju’ dilakukan perubahan / update data (misal tadinya data di kolom ‘terjual’ adalah 5, kemudian dirubah menjadi 200)