Inventory Control System

Introduction
In this article I will explain how to create a simple Inventory Management transaction using an Insert, Update, and Delete Trigger. In many projects like MES, ERP, SAP, WMS, and so on Inventory Management takes the major role. In one of my projects I was creating an Inventory Management system using SQL Triggers. I planned to create simple Transaction tables. This article explains step-by-step how to create an inventory table and trigger for inserting, updating, and deleting a Transaction table. Here I have used SQL Server 2008.
Step 1: Create table for Transaction Master. Here my Table Name is T_IO (Transaction In/Out QTY). Here you can see each column on T_IO table and the purpsose of the column in detail.
IPCL_Date (This is for Transaction Date)
IPCL_Seq ( Seq No)
GUBUN (This is a Char for Storing the Status as I for Input and O for Output)
Prod_NO (This is the Item_No or Item_Name)
Qty (This is for In/Out Qty)
Table for create script for T_IO:
CREATE TABLE T_IO
(
IPCL_Date datetime null,
IPCL_Seq int,
GUBUN char(1),
Prod_NO varchar(10),
Qty int
);
Create Table
We need to create an audit table for all transaction results maintenance. Here my Audit table name is Subul (Subul is Korean name which means Inventory Management).
Here you can see each column on SUBUL table and the pupose of the column in detail.
Subul_Date (Transaction Date),
Prod_NO (This is the Item_No or Item_Name),
Gicho (This is to store the previous date remaining Qty),
IP_Qty (To store Inventory In Qty) ,
CL_Qty (To store Inventory OutQty),
GIMAL (this is Transaction result of every record - Gicho+IP_Qty-CL-Qty)
Script for Subul table Create:
CREATE TABLE Subul
(
Subul_Date datetime null,
Prod_NO varchar(10),
Gicho int,
IP_Qty int,
CL_Qty int,
GIMAL int
);
Step 2: Create Insert, Update, and Delete for the T-IO table to store the details of the transaction result data to the SUBUL (Inventory) table. We can see the details