[Contoh] Membuat Prsedur Basis Data Unknown7:33 AM 0 comments

Kali ini saya coba kasih contoh untuk prosedur Tambah (Insert), Update, Hapus (Delete), serta triger untuk pelengkapnya. Dibawah ini adalah contohnya, kalian dapat mencoba dengan menggunakan script/code dibawah ini.. Selamat Mencoba :)

Tambah

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure tambah (@A varchar(50), @B varchar(50), @C varchar(100), @D char(1), @E varchar (20), @F int)
AS
BEGIN
 insert into tb_mhs (nim, nama, alamat,kelas,jurusan,nilai)
values (@A,@B,@C,@D,@E,@F)

END
GO
isi
exec tambah '3105211034','danar','yogyakarta','B','sistem komputer','100'
-------------------------------------------------------------------------------------------
Update

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure update_mhs (@A varchar(50), @B varchar(50), @C varchar(100), @D char(1), @E varchar (20), @F int)
AS
BEGIN
update tb_mhs set nama=@B, alamat=@C, kelas=@D, jurusan=@E, nilai=@F
where nim=@A
END
GO
--------------------------------------------------------------------------------------------------------
Delete

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure delete_mhs (@A varchar(10))
AS
BEGIN
delete from tb_mhs
where nim=@A
END
GO
------------------------------------------------------------------------------------------------------------
Triger

CREATE TRIGGER tambah
   ON  tb_transaksi
   for insert
AS
BEGIN
declare @kode varchar(50)
declare @qty int
declare @stok int
select @kode=kd_barang, @qty=qty from tb_tansaksi
select @stok=stok_gudang from tb_barang where kd_barang=@kode
update tb_barang set stok_gudang=@stok-@qty where kd_barang=@kode
END
GO