Penggunaan Store Procedure dan Trigger pada SQL Server (UAS Lab. SMBD)

Baiklah, untuk kesempatan kali ini saya akan membagi sebuah ide sekaligus menyajikan program simpel untuk bisnis penjualan spesialisasi barang antik khususnya miniatur. Jadi disini saya akan bagikan cara pembuatan program yang bisa mendukung bisnis ini. Kebetulan saya disini hanya menggunakan tiga aplikasi yaitu Microsoft Access 2012, Microsoft SQL Server 2012, dan Microsoft Visual Studio 2012. Selamat Mencoba!

Database dan Tabel-Tabel Normalisasi

1. Tabel Pelanggan

2. Tabel Pesanan









3. Tabel Barang
















Store Procedure dan Trigger

Penggunaan dua istilah ini dimaksudkan untuk pengupdatean data dari bisnis ini seperti stok barang yang berkurang maupun bertambah :

ALTER TRIGGER [dbo].[kurangstokbarang] on [dbo].[TabelPesanan]
for insert
as
update brg set brg.Stok = brg.Stok-jual.Jumlah
from TabelBarang brg join inserted jual on brg.KodeBarang=jual.KodeBarang


ALTER PROCEDURE [dbo].[Prosedur]

 @KodeBarang Number,@NamaBarang Text,@HargaBarang Number,@KodeKategori Text,@Stok Number
AS
BEGIN
SET NOCOUNT ON
INSERT INTO TabelBarang 
(
KodeBarang,NamaBarang,HargaBarang,KodeKategori,Stok
)
VALUES
(
@KodeBarang,@NamaBarang,@HargaBarang,@KodeKategori,@Stok
)
END

Design dan Source Code












Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page
    Public constring As String = ConfigurationManager.ConnectionStrings("DBShopConnectionString").ConnectionString
    Public oconn As New SqlConnection(constring)
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ocmd As New SqlCommand
        oconn.Open()
        ocmd.Connection = oconn
        ocmd.CommandText = "Prosedur"
        ocmd.CommandType = CommandType.StoredProcedure
        ocmd.Parameters.Add("@KodeBarang", SqlDbType.Number).Value = TextBox1.Text
        ocmd.Parameters.Add("@NamaBarang", SqlDbType.Text).Value = TextBox2.Text
        ocmd.Parameters.Add("@HargaBarang", SqlDbType.Number).Value = TextBox3.Text
        ocmd.Parameters.Add("@KodeKategori", SqlDbType.Text).Value = TextBox4.Text
        ocmd.Parameters.Add("@Stok", SqlDbType.Number).Value = TextBox5.Text
        ocmd.ExecuteNonQuery()
        MsgBox("Berhasil")
    End Sub
End Class

Terimakasih!

Alfath Dilon

1406629490

Database Normalisasi (UTS Lab. SMBD)

Tahapan Normalisasi

1NF

Table_SewaMusik (ID_Penyewa, Nama_Penyewa, ID_AlatMusik, Nama_AlatMusik, Tipe_Harga, Sewa_Perhari)

2NF

Table_SewaMusik (ID_Penyewa, Nama_Penyewa, ID_AlatMusik, Tipe_Harga)

Table_Merk (ID_AlatMusik, Nama_AlatMusik)

Table_TipeHarga (Tipe_Harga, Sewa_Perhari)

*note: 3NF sama dengan 2NF

Berikut Screenshot:

Table_SewaMusik

Table_Merk

Table_TipeHarga

Table_Penyewaan

Relationships

Source Code

Imports System.Data

Imports System.Data.OleDb

Public Class WebForm1

    Inherits System.Web.UI.Page

    Public constring As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

    Public oConn As New OleDbConnection(constring)

    Public oTbl As New DataTable

    Public xReader As OleDbDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim sql As String = "INSERT INTO Table_SewaMusik VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"

        Dim oCmd As New OleDbCommand

        oConn.Open()

        oCmd.Connection = oConn

        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim hapus = MsgBox("Anda Yakin", MsgBoxStyle.YesNo, "Hapus")

        If hapus = vbYes Then

            oConn.Close()

            oConn.Open()

            Dim delet As String = "DELETE FROM Table_SewaMobil where ID_Penyewa =" + TextBox1.Text + ""

            Dim oCmd As New OleDbCommand

            oConn.Close()

            oConn.Open()

            oCmd.Connection = oConn

            oCmd.CommandText = delet

            oCmd.ExecuteNonQuery()

            MsgBox("Sudah terhapus", vbArchive)

            TextBox1.Text = ""

            TextBox2.Text = ""

            TextBox3.Text = ""

            TextBox4.Text = ""

        End If

    End Sub

    Protected Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

        Dim sql As String = " UPDATE Table_SewaMusik set Nama_Penyewa  ='" & TextBox2.Text & "', ID_AlatMusik = " & TextBox3.Text & ", Tipe_Harga = '" & TextBox4.Text & "' where ID_Penyewa = " & TextBox1.Text & ""

        Dim oCmd As New OleDbCommand

        oConn.Close()

        oConn.Open()

        oCmd.Connection = oConn

        oCmd.CommandText = sql

        oCmd.ExecuteNonQuery()

    End Sub

End Class