Definisi, Sintak SQL, dan Contoh dari Jenis Database Trigger, Store Procedure, dan View

A.      Triger

   1.   Definisi Triger

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.

2.    Sintak SQL  dalam Triger.

Bentuk dasar perintah dari Triger : a. Sintak Membuat Trigger

CREATE TRIGGER nama_trigger

ON nama_tabel

FOR INSERT, UPDATE, DELETE

AS

isi statement-statement Anda disini.

GO

b. Sintak Membuat dan Mengisi Tabel pada Trigger

Setelah semuanya dijalankan di Query Analyzer, berikutnya kita akan membuat suatu trigger di tabel daftarNilai.

CREATE TRIGGER tr_status     

ON daftarnilai

FOR INSERT, UPDATE

AS

DECLARE @kode char(4)

DECLARE @nilai float

SELECT @kode = kode, @nilai = nilai FROM daftarNilai

IF @nilai >= 60

UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode

ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode

go

Untuk menganalisa lihat isi kedua tabel diatas dengan perintah.

SELECT* FROM daftarSiswa

SELECT* FROM daftarNilai

Setelah Anda melihat hasilnya kemudian jalankan perintah dibawah ini :

INSERT INTO daftarNilai values(’1001′,70)

INSERT INTO daftarNilai values(’1002′,50)

INSERT INTO daftarNilai values(’1003′,80)

                     3. Contoh Trigger

a.       Contoh table berdasarkan sintak Membuat dan Mengisi Tabel

b.      Contoh table hasil dari sintak Membuat dan Mengisi Tabel serta Sintak untuk Menganalisa Isi Tabel

Disini terlihat saat kita melakukan perintah INSERT di tabel daftarNilai (gambar 1.4) secara otomatis program trigger melakukan pengisian pada tabel daftarSiwa pada kolom “status”.

B.    Store Procedure.

  1.  Definisi Store Procedure

Procedure Adalah program yang disimpan dalam database seperti halnya data. Hal ini sebenanya cukup tidak umum, karena kita mengharapkan yang disimpan dalam database adalah data bukannya program.

Adanya dukungan Stored Procedure akan membuat program anda lebih ringkas dan mudah untuk dikembangkan.

Stored procedure akan membantu anda dalam membuat laporan yang sifatnya analisa data, yang biasanya memerlukan banyak sekali tabel2 pembantu. . Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja.

2.      Sintak SQL dalam Store Procedure

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

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.

3.      Contoh Store Procedure.

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). Format 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

C.       View

1.        Definisi View

Definisi view adalah hasil (result) dari sebuah Query terhadap relasi- relasi dasar (atau relasi real). Hasil (view) ini tidak disimpan dalam database seperti relasi dasar. Sebuah view adalah sebuah jendela dinamik, dalam artian bahwa ia mencerminkan semua update yang dilakukan terhadap database. Disamping pemakaiannya di dalam skemaexternal, view juga berguna untuk menjamin data- security dengan cara yang sederhana. Dengan memilih subset dari database, view dapat menyembunyikan beberapa data.

2.         Sintak SQL pada View

set pagesize 500

alter session set nls_date_format = 'DD.MM.YYYY';

create table prices_ (

sku        varchar2(38),

price      number,

valid_from date

);

insert into prices_ values ('4711', 18, '08.01.2003');

insert into prices_ values ('4711', 19, '01.05.2000');

insert into prices_ values ('4711', 20, '18.01.2001');

insert into prices_ values ('4711', 21, '09.01.2000');

insert into prices_ values ('beer', 14, '07.03.2000');

insert into prices_ values ('beer', 15, '10.01.2003');

insert into prices_ values ('beer', 16, '18.01.2001');

insert into prices_ values ('beer', 19, '16.11.2001');

insert into prices_ values ('beer', 17, '19.02.2002');

create view prices_today_ as

select

  sku,

  price,

  valid_from

from

  (select

     sku,

     price,

     valid_from,

     rank() over (partition by sku order by valid_from desc) r

   from

     prices_

  )

where r=1;

select * from prices_today_;

drop table prices_;

drop view prices_today_;