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 Trigge
SintakSQL dalam Triger.
Bentuk dasar perintah dari Triger.
Ke-1
CREATE TRIGGER nama_trigger
ON nama_tabel
FOR INSERT, UPDATE, DELETE
AS
isi statement-statement Anda disini.
GO
Ke-2
Cara membuat dan mengisi tabe lpada trigger , disini kita mengambil table Daftar Siswa dan table Daftar Nilai :
CREATE TABLE daftar Siswa (kodechar(4), status char(10))
CREATE TABLE daftarnilai (kodechar(4), nilai float)
Pada tabel Daftar Siswa diisi dengan perintah berikut :
INSERT INTO daftarSiswaVALUES(’1001′,”)
INSERT INTO daftarSiswaVALUES(’1002′,”)
INSERT INTO daftarSiswaVALUES(’1003′,”)
Ke-3
Setelah semuanya dijalankan di Query Analyzer, berikutnya kita akan membuat suatu trigger di tabel daftar Nilai.
CREATE TRIGGER tr_status
ON daftar nilai
FOR INSERT, UPDATE
AS
DECLARE @kodechar(4)
DECLARE @nilai float
SELECT @kode = kode, @nilai = nilai FROM daftar Nilai
IF @nilai>= 60
UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode
ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode
go
Ke-4
Untuk menganalisa lihat isi ke dua tabel diatas dengan perintah.
SELECT* FROM daftar Siswa
SELECT* FROM daftar Nilai
Setelah Anda melihat hasilnya kemudian jalankan perintah dibawah ini :
INSERT INTO daftarNilaivalues(’1001′,70)
INSERT INTO daftarNilaivalues(’1002′,50)
INSERT INTO daftarNilaivalues(’1003′,80)
Contoh Triger
table berdasarkan sintak Ke-2
| DaftarNilai | |
| Kode | Nilai |
| DaftarSiswa | |
| Kode | Status |
| 1001 | |
| 1002 | |
| 1003 | |
Contoh table hasil dari sintak Ke-2, Ke-3 dan Ke-4
| DaftarNilai | |
| Kode | Nilai |
| 1001 | 70 |
| 1002 | 50 |
| 1003 | 80 |
| DaftarSiswa | |
| Kode | Status |
| 1001 | Lulus |
| 1002 | Tidak Lulus |
| 1003 | Lulus |
Store Procedure.
· Definisi Store Procedure
Procedure Adalah program yang disimpan dalam database sepertihalnya 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 banyaks ekali tabel2 pembantu.. Stored procedure mengefisienkan proses, sehingga semua daya hanya akan dipusatkan di komputer server saja.
· Sintak SQL dalam Store Procedure
CREATE PROCEDURE nama_stored_procedurenya @parameter1 tipe_data, @parameter2 2 tipe_data AS isiprocedurenya.
Penjelasan
1.namastored_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 gunaka nperintah CREATE, untuk mengedit gunakanALTER ,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. Untukdeklarasi parameter di Stored Procedure gunakan DECLARE
contoh : DECLARE @StartDatedatetime, @EndDatedatetime, dst..
5. Untukmemasukkannilaikesebuah parameter, gunakan SET atau SELECT, contoh :
SET @nama='Itanium'
SET @Web='Klik-kanan' (perintah SET hanyabisauntuk 1 variabelsaja)
SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisadigunakanuntukbanyakvariabel)
1.namastored_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 gunaka nperintah CREATE, untuk mengedit gunakanALTER ,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. Untukdeklarasi parameter di Stored Procedure gunakan DECLARE
contoh : DECLARE @StartDatedatetime, @EndDatedatetime, dst..
5. Untukmemasukkannilaikesebuah parameter, gunakan SET atau SELECT, contoh :
SET @nama='Itanium'
SET @Web='Klik-kanan' (perintah SET hanyabisauntuk 1 variabelsaja)
SELECT @nama='Itanium', @Web='Klik-kanan' (perintah SELECT bisadigunakanuntukbanyakvariabel)
6. Untukmengambilnilaidarisebuah field daritabelkedalamvariabeldapatjugamenggunakan SET / SELECT ,misal : SET @nama= SELECT nama FROM user WHERE login='Itanium') selalugunakananda(),perintahini valid bila data yang ditemukanhanya 1, bilalebih, makavaribale @namatidakakanadanilainya.
7. Untukmengambil data dalamjumlahbanyak, misalseperti array atauStringList, gunakan temporary tabel.untukpembahasanlebihlengkap, tunggu posting berikutnya.
Contohpembuatan Stored Procedure. Kita akanmembuatsebuah Stored procedure yang berfungsiuntukmenggantikanperintah SELECT yang rumit, misaluntuklaporanstokbarang.
· Contoh Store Procedure.
Dalamcontohiniada 3 tabel yang digunakan :
1. Barang (IDBarang, NamaBarang, IDSatuan).
2. Satuan (IDSatuan, Satuan).
3. StockBarang (Tanggal, IDBarang, SAwal,Masuk,Keluar,SAkhir) laporan yang dimintaadalahuntukmenampilkan stock sesuaidenganperiodetertentu (bisa per hari, bisajuga per minggu, tergantunginputanStartDatedanEndDate). SQL untukpembuatanStored Procedure nya :
CREATE PROCEDURE LapStockBarang1 @StartDatevarchar(10), @EndDatevarchar(10) AS
DECLARE @tgl1 datetime, @tgl2 datetime
**(variabelStartDatetidakbisalangsungdatetimekarenaperintah EXEC LapStockBarangdalambentuk string)
CREATE TABLE #TStock (IDBarangvarchar(5), SAwal real, Masuk real, Keluar real)
CREATE TABLE #TStock2 (IDBarangvarchar(5), SAwal real, Masuk real, Keluar real, SAkhir real)
** buat temporary table , tanda # menandaibahwatabelinihanyaakanadasaat proses stored procedure.
SELECT @tgl1= CONVERT(datetime,@StartDate,103), @tgl2=CONVERT(datetime,@EndDate,103)
** convert varchar(string) ketipedatetime, gunakanperintah CONVERT(tipe,variabel,format). Format 103 adalah format dd/mm/yyyy
INSERT INTO #TStock (IDBarang, SAwal)
SELECT (IDBarang, SAwal)
FROM StockBarang
WHERE tanggal= @tgl1
** masukkansaldoawalpadatanggalbulanitu
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 darimasukdankeluar
INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar)
SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar)
FROM #TStock
GROUP BY IDBarang
** sekaranggabungkan 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
**(variabelStartDatetidakbisalangsungdatetimekarenaperintah EXEC LapStockBarangdalambentuk string)
CREATE TABLE #TStock (IDBarangvarchar(5), SAwal real, Masuk real, Keluar real)
CREATE TABLE #TStock2 (IDBarangvarchar(5), SAwal real, Masuk real, Keluar real, SAkhir real)
** buat temporary table , tanda # menandaibahwatabelinihanyaakanadasaat proses stored procedure.
SELECT @tgl1= CONVERT(datetime,@StartDate,103), @tgl2=CONVERT(datetime,@EndDate,103)
** convert varchar(string) ketipedatetime, gunakanperintah CONVERT(tipe,variabel,format). Format 103 adalah format dd/mm/yyyy
INSERT INTO #TStock (IDBarang, SAwal)
SELECT (IDBarang, SAwal)
FROM StockBarang
WHERE tanggal= @tgl1
** masukkansaldoawalpadatanggalbulanitu
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 darimasukdankeluar
INSERT INTO #TStock2 (IDBarang, SAwal, Masuk, Keluar)
SELECT IDBarang, SUM(SAwal), SUM(Masuk), SUM(Keluar)
FROM #TStock
GROUP BY IDBarang
** sekaranggabungkan 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
View
Definisi View
Definisi view adalah hasil (result) dari sebuah Query terhadap relasi- relasi dasar (ataurelasi 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 dalams kemaexternal, view juga berguna untuk menjamin data- security dengan cara yang sederhana. Dengan memilih subset dari database, view dapat menyembunyikan beberapa data.
· Sintak SQL pada View
setpagesize 500
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
selectsku,price,valid_fromfrom (selectsku,price,valid_from,rank() over (partition by sku order by valid_fromdesc) r
from
prices_
)
where r=1;
select * from prices_today_;drop table prices_;drop view prices_today_;