Database of Scriptum

This time we will discuss about the computerization of inventory in the warehouse (location) is done automatically at the time the transaction is entered into a purchase transaction table, into the sales transaction table, as well as mutations in the transaction table movement of goods between warehouses (locations). The assumption of this discussion is about the stock of drugs in the hospital pharmacy, the location of the drug in some places.
For the above, the following is the syntax of SQL that is run on Microsoft SQL Server 2000 Personal Edition.
CREATE TABLE [dbo].[Master_Obat] (
    [Kode_Kelompok] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Kode_Obat] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Nama_Obat] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Satuan] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Harga_beli] [money] NULL ,
    [Harga_Jual] [money] NULL ,
    [Kode_Pabrik] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
    CONSTRAINT [PK_Master_Obat] PRIMARY KEY  CLUSTERED
    (
        [Kode_Obat]
    )  ON [PRIMARY]
GO
---------------------------------------------------
Master_obat tables are used to store master data medicine overall. In this table has a field that is drug code, drug name, unit, purchase price, sales price, and several other fields as needed.
---------------------------------------------------

CREATE TABLE [dbo].[Gudang] (
    [kode_gudang] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Nama_gudang] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Is_GudangUtama] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
    CONSTRAINT [PK_Master_Obat] PRIMARY KEY  CLUSTERED
    (
        [Kode_gudang]
    )  ON [PRIMARY]
GO
---------------------------------------------------

CREATE TABLE [dbo].[StokGudang] (
    [kode_gudang] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [kode_obat] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [stok] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
    CONSTRAINT [PK_Master_Obat] PRIMARY KEY  CLUSTERED
    (
        [Kode_gudang]
        [Kode_obat]
    )  ON [PRIMARY]
GO
----------------------------------------------
Both of the above table is used to store information drug stocks at each location.
----------------------------------------------

Next is a purchase transaction table as shown below. At the time of the purchase transaction information is stored in two tables, namely, purchase table and table purchase details.

CREATE TABLE [dbo].[Pembelian] (
    [Nomor_Pembelian] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Nomor_Faktur] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Tanggal_Pembelian] [datetime] NULL ,
    [pemasok] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Master_Obat] ADD
    CONSTRAINT [PK_Master_Obat] PRIMARY KEY  CLUSTERED
    (
        [Nomor_Pembelian]
    )  ON [PRIMARY]
GO

-------------------------------------------------------
CREATE TABLE [dbo].[Pembelian_Detail] (
    [Nomor_Pembelian] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Kode_Obat] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [quantity] [int] NOT NULL ,
    [Harga_beli] [money] NULL ,
    [harga_jual] [money] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Pembelian_Detail] ADD
    CONSTRAINT [PK_Pembelian_Detail] PRIMARY KEY  CLUSTERED
    (
        [Nomor_Pembelian],
        [Kode_Obat]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Pembelian_Detail] ADD
    CONSTRAINT [FK_Pembelian_Detail_Pembelian] FOREIGN KEY
    (
        [Nomor_Pembelian]
    ) REFERENCES [dbo].[Pembelian] (
        [Nomor_Pembelian]
    )
GO

Drug stocks counting mechanism through trigger on the table purchase details, as described below. Trigger occurs when the INSERT, DELETE, and UPDATE.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER tbl_PembelianDetail_Trigger1
ON dbo.Pembelian_Detail
FOR INSERT
AS
    declare @no_beli     varchar(12)
    declare @kod_obt     nvarchar(8)
      declare @qty     int
    declare @stk     int
    declare @hrg_beli    money
    declare @hrg_jual    money

    SELECT     @no_beli  = ins. nomor_pembelian,
        @kod_obt = ins.kode_obat,
        @qty = ins.quantity,
        @hrg_beli = ins.harga_beli,
        @hrg_jual = ins.harga_jual
    FROM inserted ins

    SELECT @stk = Stok
    FROM StokGudang
    WHERE (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obt)

    UPDATE StokGudang
    SET stok = @stk + @qty
    WHERE  (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obt)

    UPDATE master_obat
    SET harga_beli = @hrg_beli, harga_jual = @hrg_jual
    WHERE kode_obat = @kod_obt


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER tbl_PembelianDetail_Trigger2
ON dbo.Pembelian_Detail
FOR DELETE
AS
    declare @no_beli     varchar(12)
    declare @kod_obt     nvarchar(8)
      declare @qty     int
    declare @stk     int

    SELECT     @no_beli  = del. nomor_pembelian,
        @kod_obt = del.kode_obat,
        @qty = del.quantity
    FROM deleted del

    SELECT @stk = Stok
    FROM StokGudang
    WHERE  (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obt)

    UPDATE StokGudang
    SET stok = @stk - @qty
    WHERE  (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obt)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER tbl_PembelianDetail_Trigger3
ON dbo.Pembelian_Detail
FOR UPDATE
AS
    declare @no_beli     varchar(12)
    declare @kod_obtIns     nvarchar(8)
    declare @kod_obtDel     nvarchar(8)
      declare @qtyIns     int
    declare @qtyDel     int
    declare @stk     int
    declare @hrg_beliIns    money
    declare @hrg_jualIns    money
    declare @hrg_beliDel    money
    declare @hrg_jualDel    money

     SELECT     @no_beli  = ins. nomor_pembelian,
        @kod_obtDel = del.kode_obat,
        @kod_obtIns = ins.kode_obat,
        @qtyDel = del.quantity,
        @qtyIns = ins.quantity,
        @hrg_beliDel = del.harga_beli,
        @hrg_beliIns = ins.harga_beli,
        @hrg_jualDel = del.harga_jual,
        @hrg_jualIns = ins.harga_jual
    FROM deleted del, inserted ins

    SELECT @stk = Stok
    FROM StokGudang
    WHERE (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obtDel)

    UPDATE StokGudang
    SET stok = @stk - @qtyDel
    WHERE (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obtDel)

    SELECT @stk = Stok
    FROM StokGudang
    WHERE (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obtIns)

    UPDATE StokGudang
    SET stok = @stk + @qtyIns
    WHERE (kode_gudang = (SELECT Kode_Gudang
                   FROM Gudang
                   WHERE Is_gudangUtama = 1))
         AND (kode_obat = @kod_obtIns)
   
    UPDATE master_obat
    SET harga_beli = @hrg_beliDel, harga_jual = @hrg_jualDel
    WHERE kode_obat = @kod_obtDel

    UPDATE master_obat
    SET harga_beli = @hrg_beliIns, harga_jual = @hrg_jualIns
    WHERE kode_obat = @kod_obtIns


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------