Thursday, January 20, 2011

Trigger

TRIGGER

Definisi Trigger
Trigger adalah batch/sekumpulan perintah yang secara otomatis dikerjakan ketika terjadi penyisipan (insert), pengubahan (update) atau penghapusan data (delete) pada sebuah tabel.
Sintaks penulisan :
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ delete ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
Latihan :
1. Buat DBTrigger
2. Buat Tabel TBNilai dengan struktur berikut :

3. Buat Trigger Insert trgInsert_Nilai
Contoh Trigger Insert pada table TBNilai :
Ketika kita mengisi data baru dengan memasukkan nilai UTS atau UAS pada tabel bernama TBNilai, maka secara otomatis NAkhir dihitung ulang dengan rumus = 40% UTS + 60 % UAS
CREATE trigger [dbo].[trgINSERT_Nilai] on [dbo].[TBNILAI]
for INSERT
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
update tbNilai
set NAkhir=CONVERT(INT, 0.4*UTS + 0.6*UAS)
where KdMK=@v1 and NIM=@v2
return
4. Lakukan insert pada table tersebut sbb, NAkhir jangan diisi krn akan diupdate otomatis oleh Trigger.


5. Buat Trigger Update pada TBNilai dengan contoh berikut :
Ketika kolom UTS atau UAS pada tabel bernama TBNilai dilakukan perubahan, maka secara otomatis NAkhirnya dihitung ulang dengan rumus = 40% UTS + 60 % UAS

CREATE trigger trgNilai on TBNilai for update
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
if update(UTS) or update(UAS) begin
update TBNilai
set NAkhir=0.4*UTS + 0.6*UAS
where KdMK=@v1 and NIM=@v2
end
return

6. Untuk menguji TrgNilai tadi (Trigger untuk update pada TBNILAI), buatlah sintaks SQL berikut :



Pembahasan di atas, kita telah melakukan percobaan dengan membuat Trigger untuk Insert dan Update. Kedua trigger dibuat pada table TBNilai. Trigger tersebut akan mengupdate NAkhir pada tabel yang sama (dirinya sendiri), seperti tampak pada ilustrasi berikut :








TRIGGER UNTUK MENCATAT PERUBAHAN

LATIHAN :
1. Buat table TBPegawai sbb :



2. Buat tabel TBLog sbb :




3. Buat Trigger Insert pada TBPegawai sbb :


4. Pengujian TRInsert_Pegawai


5. Buat Trigger untuk Update pada TBPegawai sbb :







6. Buat Trigger untuk Delete pada TBPegawai :

7.

Transaction

TRANSACTION
1. Apa itu transaction?
Sebuah transaksi adalah sekelompok perintah yang harus dijalankan, hasilnya berhasil atau gagal untuk semua perintah tersebut
2. Apa pentingnya transaction?
Lihat contoh berikut : missal diperlukan perubahan stok setiap kali ada penjualan 1 item barang

Update Inventory
Set qty = qty – 1
Where item_id=2012

Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)

BEGIN TRAN
Update Inventory
Set qty = qty – 1
Where item_id=2012

Insert Sales_detail (item_id, inv_id, datesold, qty)
Values(2012, 308, current_timestamp, 1)
COMMIT TRAN

LATIHAN 1:
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'

update FB
set tempo=300 where KODEPELANGGAN='S-00003'

--LIHAT DATA SESUDAH DIUPDATE
select * from FB

--LAKUKAN ROLLBACK
ROLLBACK TRAN

--LIHAT DATA SESUDAH ROLLBACK
select * from FB

HASILNYA :


LATIHAN 2 :
--LIHAT DATA SEBELUM DIUPDATE
select * from FB
BEGIN TRAN
--LAKUKAN UPDATE
update FB
set tempo=200 where KODEPELANGGAN='S-00002'

update FB
set tempo=300 where KODEPELANGGAN='S-00003'

--LIHAT DATA SESUDAH DIUPDATE
select * from FB

--LAKUKAN COMMIT
COMMIT

--LIHAT DATA SESUDAH COMMIT
select * from FB

HASILNYA :


LATIHAN 3 :
select * from fb
BEGIN TRAN
insert into fb(NoFJ,KodePelanggan,tglFJ,totalfaktur)
VALUES ('FB-3','S-00002','2007-11-06 12:12:12','10000')

update Supplier
set Piutang = Piutang+10000 where KODEPELANGGAN ='S-00002'

select * from fb
select * from supplier

COMMIT
HASILNYA


1. Apa yang dihasilkan jika perintah ini dijalankan
-- Create table to work with
CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
)
select * from mytrantest ---------------------(A)

BEGIN TRAN TranStart
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(B)


SAVE TRAN FirstPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(C)

ROLLBACK TRAN FirstPoint
select * from mytrantest ---------------------(D)

INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(E)

SAVE TRAN SecondPoint
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(F)


ROLLBACK TRAN SecondPoint
select * from mytrantest
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(G)


COMMIT TRAN TranStart
SELECT TOP 3 OrderID
FROM MyTranTest
ORDER BY OrderID ASC ---------------------(H)

Cursor

CURSOR
Apa itu CURSOR?
Cursor adalah suatu perintah pemrograman yang mengijinkan setiap baris (record) diproses secara terpisah terhadap record lainnya. Cursor sangat bermanfaat jika ada suatu proses yang rumit yang ingin dilakukan terhadap suatu record. Proses tersebut bisa dilakukan secara satu per satu, yatiu lewat cursor
Algoritma CURSOR ?
1. Mendeklarasikan Cursor
2. Membuka Cursor
3. Memproses Cursor
4. Menutup Cursor
5. Mendealokasikan Cursor
Penggunaan CURSOR yang sederhana
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select * from Msiswa Where NIK>=2 and NIK <=4

--2 Membuka Cursor
OPEN CsrMsiswa


--3 Memproses Cursor
DECLARE @NIK int
DECLARE @NAMA char(30)

FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'NIK :'+CONVERT(CHAR,@NIK)
PRINT 'NAMA :'+@NAMA

FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END

--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
Menggunakan Cursor pada Store Procedure
Untuk membuat SP gunakan perintah Create Procedure as. Kemudian salin 5 langkah penggunaan cursor tadi.


Menjalankan SP yang didalamnya ada Cursor


Latihan Cursor : Penggunaan Cursor untuk Pencetakan Struk Gaji
Database : PTABC
Tabel : Karyawan , dengan struktur dan datanya sbb (LIHAT GAMBAR DIBAWAH INI)

PENGGUNAAN SCROLLABLE CURSOR
Ada lima tipe scrollable cursor yang data digunakan saat memproses cursor yaitu :
- Prior
- Next
- First
- Last
- Relative (+/- n)
Untuk mengaktifkan scrollable cursor tambahkan sintak SCROLL saat mendeklarasikan CURSOR, sbb :
--1.Deklarasi cursor
DECLARE CsrKaryawan SCROLL CURSOR
FOR select NIK, NAMA from Karyawan

--2.Buka cursor
Open CsrKaryawan

--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30)

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

--4.Tutup Cursor
Close CsrKaryawan

--5.Dealokasi Cursor
Deallocate CsrKaryawan
Contoh penggunaan scrollable cursor


Latihan :
1. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
2. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
3. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH RELATIVE -2 FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA
FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

4. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

5. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

6. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH RELATIVE 2 FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

7. Coba gunakan scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

8. Coba gunakan TANPA scrollable cursor (isikan pada bagian –3. Proses Cursor), dan perhatikan hasilnya :
FETCH LAST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH FIRST FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH PRIOR FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

CATATAN :
Untuk sintaks PRIOR, LAST, FIRST, RELATIVE hanya bisa digunakan pada SCROLLABLE CURSOR

LATIHAN :
1. BUAT SP_SCR_CURSOR1, HASIL SBB :
NIK NAMA
1001
1002
1003
1004
1003
1002
1001 A
B
C
D
C
B
A




2. BUAT SP_SCR_CURSOR2, HASIL SBB :
NO DEP NIK NAMA GAJI TRANSPORT TOTAL
1 HRD 1001 A 1000 500 1500
2 1002 B 1000 500 1500
-----------------SUBTOTAL 2000 1000 3000

3 IT 1003 C 2000 750 2750
4 1004 D 2000 750 2750
-----------------SUBTOTAL 4000 1500 5500

-----------------GRANDTOTAL 6000 2500 8500




3. BUAT SP_SCR_CURSOR3 untuk mengisi tabel RekapGaji, sbb
Tabel RekapGaji
DEPARTEMEN JLHKARYAWAN GAJI TRANSPORT
HRD 2 2000 1000
IT 2 4000 1500

4. Buat SP_RekapIPK
Tabel Mahasiswa
NIK NAMA IPK
01
01
03
04
05
06 A
B
C
D
E
F 3,50
2,80
2,50
2,00
1,90
3,10

RekapIPK : ini dihasilkan setelah sp dijalankan
RENTANGA RENTANGB JUMLAHMHS
0,00 2,00
2,01 3,00
3,01 4,00

Wednesday, January 12, 2011

Blog Creator

Siswanto Budi Prayogo 0972002

Contoh Soal View

1)Pelanggan yang tidak bertransaksi di bulan juni 2005

SELECT dbo.Pelanggan.KodePelanggan, dbo.Pelanggan.NamaPelanggan, dbo.FJ.TglFJ
FROM dbo.Pelanggan LEFT OUTER JOIN
dbo.FJ ON dbo.Pelanggan.KodePelanggan = dbo.FJ.KodePelanggan
WHERE (dbo.Pelanggan.KodePelanggan NOT IN
(SELECT KodePelanggan
FROM dbo.FJ AS FJ_1
WHERE (TglFJ BETWEEN '6/1/2005' AND '6/30/2005 23:59:59')))

2) Buat view barang barang yang terjual di juni dan juli 2005 khusus yang bertanda cek


SELECT DISTINCT dbo.Barang.KodeBarang, dbo.Barang.NamaBarang, dbo.FJ.TglFJ, dbo.Barang.Cek
FROM dbo.Barang INNER JOIN
dbo.FJDet ON dbo.Barang.KodeBarang = dbo.FJDet.KodeBarang INNER JOIN
dbo.FJ ON dbo.FJDet.NoFJ = dbo.FJ.NoFJ
WHERE (dbo.FJ.TglFJ BETWEEN '6/1/2005' AND '7/31/2005 23:59:59') AND (dbo.Barang.Cek = 1)

Contoh-contoh SP dan AutoNumber

ALTER PROCEDURE SP_InsertDaftar

@nama varchar(50),
@lp varchar(50),
@tgldaftar datetime,
@jurusan varchar(50)

AS
BEGIN

declare @nodaftar varchar(50)
declare @noakhir int
declare @nobaru int

select @noakhir = Max(Convert(int,substring(NoDaftar,8,4))) from TDaftar

if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end

set @nodaftar = Convert(varchar,datepart(yyyy,@tgldaftar)) + '/' +
@lp + '-' + right('0000' + Convert(varchar,@nobaru),4) + '/' +
@jurusan

insert into TDaftar
values(@nodaftar,@nama,@lp,@tgldaftar,@jurusan)

END
GO

exec SP_InsertDaftar 'Test3','P','1/1/2011','DK'
select * from TDaftar

-------------------------------------------------------------------------------------

CREATE PROCEDURE SP_InsertPegawai

@thnmasuk datetime,
@nama varchar(50)

AS
BEGIN

declare @nopegawai varchar(50)
declare @noakhir int
declare @nobaru int

select @noakhir = Max(Convert(int,substring(NoPegawai,1,5))) from TPegawai

if(@noakhir is null)
begin
set @nobaru = 1
end
else
begin
set @nobaru = @noakhir + 1
end

set @nopegawai = right('00000' + Convert(varchar,@nobaru),5) +
'-' + Convert(varchar,datepart(yyyy,@thnmasuk)) + '-' +
substring(@nama,1,1)

insert into TPegawai
values(@nopegawai,@thnmasuk,@nama)

END
GO

exec SP_InsertPegawai '5/6/2005','Bebas'
select * from TPegawai

-------------------------------------------------------------------------------------

CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END

exec SP_TambahTBPO'6/1/2005', 'S-00005'



-------------------------------------------------------------------------------------

ALTER PROCEDURE SP_TambahGudang
@TglPO datetime, @Gudang varchar(10), @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(50)

--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(NomorPO,5))))
from TGudang
where (substring(NomorPO,4,4) = convert(varchar(4), Datepart(yyyy, @TglPO)))and (substring(NomorPO,9,2) = Convert(varchar(2),@Gudang))

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(varchar(4), Datepart(yyyy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar(5), @NoBaru), 5)


--3.Insert Data baru
Insert TGudang (NomorPO, TglPO, Gudang, KodeSupplier) Values (@NomorPO, @TglPO, @Gudang, @KodeSupplier)
END

exec SP_TambahGudang '10/16/2010','G3','SS'

-------------------------------------------------------------------------------------

ALTER PROCEDURE SP_TambahBarang
@NamaBarang varchar(50), @Stock int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Kode varchar(50)

--1.Cari Nomor Akhir
select @NoAkhir = Max(convert(int,(right(KodeBarang,3))))
from TBarang
where left(KodeBarang,1) = left(@NamaBarang,1)

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @Kode = left(@NamaBarang,1) + RIGHT('000'+Convert(varchar(5), @NoBaru), 3)


--3.Insert Data baru
Insert TBarang
Values (@Kode, @NamaBarang, @Stock)
END

exec SP_TambahBarang 'Bath Tub Toto 34523',5
select * from TBarang

Quiz Sebelum UTS

2. SP_Lihat_Pasien

CREATE PROCEDURE SP_Lihat_Pasien
@tahun char(2),
@bulan char(2)
AS
BEGIN

SELECT * from t_pasien
where substring(id_pasien,2,2) = @tahun and
substring(id_pasien,4,2) = @bulan
END
GO

3. SP_Lihat_Gologan Darah

CREATE PROCEDURE SP_Lihat_GolDarah
@gol_darah char(2)
AS
BEGIN

SELECT * from t_pasien
where gol_darah = @gol_darah
END
GO

4.SP_Tambah_Pasien


alter PROCEDURE SP_Tambah_Pasien
@nama varchar(30)

AS
BEGIN

declare @yy varchar(2)
declare @mm varchar(2)
declare @id_pasien varchar(10)
declare @no_akhir int
declare @no_baru int

set @yy = substring(Convert(varchar,(datepart(yy,getdate()))),3,2)


set @mm = right('0' + Convert(varchar,(datepart(mm,getdate()))),2)

select @no_akhir = Max(Convert(int,right(id_pasien,3))) from t_pasien
where substring(id_pasien,2,2) = @yy and
substring(id_pasien,4,2) = @mm
if(@no_akhir is null)
begin
set @no_baru = 1
end
else
begin
set @no_baru = @no_akhir + 1
end
print @no_baru
set @id_pasien = 'P'+ @yy + @mm + right('000' + Convert(varchar,@no_baru),3)

print @id_pasien

INSERT INTO [rumahsakit].[dbo].[t_pasien]
([id_pasien]
,[nama])
VALUES
(@id_pasien
,@nama)


END
GO


5. a

alter FUNCTION HitungUmur
(
@p1 datetime
)
RETURNS varchar(20)
AS
BEGIN

DECLARE @Result1 varchar(10)
DECLARE @Result2 varchar(10)
declare @result varchar(20)

set @Result1 = Convert(varchar,datediff(yy,@p1,getdate())-1)
set @Result2 = Convert(varchar,datediff(mm,@p1,getdate())-@Result1*12)

set @result = @Result1 + ' Tahun ' + @Result2 + ' Bulan'

return @result

END
GO

b.
create FUNCTION HitungDokter
(
@p1 varchar(50)
)
RETURNS int
AS
BEGIN

DECLARE @Result int

select @Result = count(id_dokter) from t_dokter where spesialisasi like '%' + @p1 + '%'

return @Result

END
GO