Praktikum Pemrograman Basis Data (Pertemuan 6)

Bagi sobat duniaku yang kuliah di Stikom Prak. PBD ini adalah mata kuliah di semester 3. monggo di pelajari source code jawaban pertemuan 6 :


alter trigger after_insert on detil_peminjaman
for insert as
declare @mhs varchar(11), @id_buku int
set @mhs = (select mahasiswa from peminjaman, inserted where peminjaman.no_peminjaman = inserted.no_peminjaman)
set @id_buku = (select inserted.id_buku from inserted)
exec upd_quotaperpus @mhs
exec upd_stok @id_buku

-----------------------------------------------------------------------------
alter procedure upd_quotaperpus(@nim varchar(11))
as
begin
declare @jum_pinjam int
set @jum_pinjam = (select count(no_peminjaman) from peminjaman where mahasiswa = @nim)
update mahasiswa set quota_perpus = quota_perpus - @jum_pinjam where nim = @nim
end

-----------------------------------------------------------------------------
alter procedure upd_stok(@id int)
as
begin
declare @stok int
set @stok = (select jumlah from buku where id = @id)
update buku set jumlah = @stok - 1 where id = @id
end


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


alter trigger before_insert on detil_peminjaman
instead of insert as
declare @jumlah_quota int, @mhs varchar(11), @no_pinjam int, @id_buku int, @denda int
set @mhs = (select mahasiswa from peminjaman, inserted where peminjaman.no_peminjaman = inserted.no_peminjaman)
set @no_pinjam = (select inserted.no_peminjaman from inserted)
set @id_buku = (select inserted.id_buku from inserted)
set @denda = (select inserted.denda from inserted)
set @jumlah_quota = (dbo.cek_quota(@mhs))

if (@jumlah_quota = 0)
begin
exec delete_all @mhs, @no_pinjam, @id_buku
end

else
begin
insert into detil_peminjaman values(@no_pinjam, @id_buku, @denda)
end

-----------------------------------------------------------------------------
alter function cek_quota (@nim varchar(11))
returns int
as
begin
declare @quota int
set @quota = (select quota_perpus from mahasiswa where nim = @nim)
return @quota
end

-----------------------------------------------------------------------------
alter procedure delete_all(@nim varchar(11), @no_pinjam int, @id_buku int)
as
begin
delete from detil_peminjaman where no_peminjaman = @no_pinjam and id_buku = @id_buku
delete from peminjaman where no_peminjaman = @no_pinjam and mahasiswa = @nim
end