在ERP或进销存软件设计时，常用常用需要知道某时的期初库存及期末库存，因而需要记录库存的实时进出。
设计一个表用于记录库存的实时进出。
- --库存表，用于存放当前库存
- IF OBJECT_ID ('Storage',N'U') IS NOT NULL
- drop table Storage
- create table Storage
- (
- ID int identity(1,1),
- ProductID int not null,--物品ID
- StorageID int not null,--仓库ID
- Amount decimal(8,2)--当前数量
- primary key(ProductID,StorageID)
- )
- --库存进出记录表，用于记录仓库的进出流水帐
- IF OBJECT_ID ('StorageInOut',N'U') IS NOT NULL
- drop table StorageInOut
- create table StorageInOut
- (
- ID int identity(1,1),
- InOutDate datetime default getdate() not null,--进出时间
- ProductID int not null,--物品ID
- StorageID int not Null,--仓库ID
- StorageIn decimal(8,2) default 0 not null,--进库数量
- StorageOut decimal(8,2) default 0 not null,--出库数量
- Amount decimal(8,2) default 0 not null--结存数量
- )
再在库存表Storage上创建一个触发器，用于把进出数据写入StorageInOut表中。
- IF OBJECT_ID ('StorageLog','TR') IS NOT NULL
- DROP TRIGGER StorageLog
- GO
- CREATE TRIGGER StorageLog
- ON Storage
- AFTER INSERT, UPDATE
- AS
- declare @ProductID int,
- @StorageID int,
- @Old decimal(8,2),
- @New decimal(8,2),
- @Amount decimal(8,2)
- select @Old=Amount from deleted
- select @ProductID=ProductID,@StorageID=StorageID,@New=Amount from inserted
- select @Amount=Amount from StorageInOut where ID = (select Max(ID)
- from StorageInOut where ProductID=@ProductID and StorageID=@StorageID)
- if @Amount is null
- set @Amount=0
- if @Old is not null
- begin
- if @New >@Old
- begin
- insert StorageInOut(ProductID, StorageID,StorageIn, StorageOut,Amount)
- values(@ProductID,@StorageID,@New-@Old,0,@Amount+@New-@Old)
- end else
- begin
- insert StorageInOut(ProductID, StorageID,StorageIn, StorageOut,Amount)
- values(@ProductID,@StorageID,0, @Old-@New,@Amount-@Old+@New)
- end
- end else
- begin
- insert StorageInOut(ProductID, StorageID,StorageIn, StorageOut,Amount)
- values(@ProductID,@StorageID,@New, 0,@Amount+@New)
- end
- GO
取某时段的期初库存量：
- select * from StorageInOut where id=(select max(id) from StorageInOut where InOutDate<'2012-10-11 22:36:44')
取某时段的出入库数量：
- select sum(storageOut) '出库量',sum(StorageIn) '入库量' from StorageInOut where InOutDate between '2012-10-11 22:36:44' and '2012-12 :23:59:59'
取某时段结存（期末）库存量：
- select * from StorageInOut where id=(select max(id) from StorageInOut where InOutDate between '2012-10-11 22:36:44' and '2012-12 :23:59:59')
这样就可以查询到任何时间的出入库数量及结存数，对移动盘点，库存管理就方便多了。
附：Sqlite 创建触发器的方法
- --创建班级表
- create table class
- (
- id integer primary key autoincrement, --班级编号
- className nvarchar(50) --班级名称
- );
- --创建学生表
- create table student
- (
- id integer primary key autoincrement, --编号
- stuName nvarchar(20), --学生名称
- stuSex bit, --性别
- stuAge integer , --年龄
- classId --班级编号
- );
- --创建插入触发器 （创建学生时要触发插入触发器去判断是否存在该班级，存在插入成功，反之插入失败）
- create trigger fk_Insert
- before insert on student
- for each row
- begin
- select raise(rollback,'还没有该班级')
- where (select id from class where id = new.classId ) is null;
- end;
- --创建更新触发器 （更新学生时要触发更新触发器去判断是否存在更新班级，存在更新成功，反之更新失败）
- create trigger fk_Update
- before update on student
- for each row
- begin
- select raise(rollback,'还没有该班级')
- where (select id from class where id = new.classId)is null;
- end;
- --创建删除触发器 （删除班级时，首先根据班级编号删除该班级学生）
- create trigger fk_Delete
- before delete on class
- for each row
- begin
- delete from student where classId = old.classId;
- end ;
- insert into class(className) values('s1t64');
- insert into student(stuName,stuSex,stuAge,classId)values('zhangsan',1,23,1);
- update student set stuName='lishi',classId=1 where id = 1;
- select * from class ;
- select * from student limit 0,100 ; -- 分页查询从索引0开始查找，100条数据
|
[复制链接]
发表于 2013-5-28 16:02:53