数据库仓库管理系统课程设计

as
delete from lj_cin where sno=@sno and number=@number
go
-- 出库数据错误删除某条出库信息
create procedure cout_worry(@sno char(8),@number int)
as
delete from lj_cout where sno=@sno and number=@number
number int,
kind char(12),
sname char (20)
)
go
--出库信息表，同时进行出库操作
create table lj_cout
(
sno char(8),
number int,
kind char(12),
sname char(20),
select lj_cool.sno as'编号',lj_cool.sname'名字',lj_cool.kind as'种类',lj_cool.number as'库存数量'
from lj_cool
wheБайду номын сангаасe sno in(
select sno
from lj_cin
where number=3
exec lj_cool_cin @a,@b,@c,@d
--出库触发器
go
create trigger insert_cout
on lj_cout
for insert
as
declare @a char(8),@b char(20),@c char(12),@d int
select @a=sno,@b=sname,@c=kind,@d=number from inserted
exec lj_cool_cout @a,@b,@c,@d
go
-- 产品为空时提醒触发器，库存没有了，求更新
create trigger null_cool
on lj_cool
for update
as
declare @a int,@c char(20)
select @a=number from inserted
)
go
--进库时更新仓库库存信息
create procedure lj_cool_cin(@sno char(8),@sname char(20),@kind char(12),@number int)
as
declare @a char(8),@b int
select @a=sno from lj_cool where sno=@sno
on lj_cout
for delete
as
declare @a char(8),@d int
select @a=sno,@d=number from deleted
update lj_cool
set number=number+@d where sno=@a
go
-- 数据
insert into lj_cin (sname,kind,sno,number ) values('螺丝','机器','001',3)
if (@a is not null)
begin
update lj_cool
set number=number-@number where sno=@a
end
go
--进库数据错误删除某条进库信息
create procedure cin_worry(@sno char(8),@number int)
From lj_cool
select * from lj_cout
select * from lj_cool
go
-- 简单查询入库出库库存三个表
select * from lj_cin
select * from lj_cout
select * from lj_cool
go
--查询单次进库数量为3的库存数据
go
--存储过程删除数据
exec cin_worry '001',3
go
--插入数据
insert into lj_cin(sno,sname,kind,number) values('324','郭修军','帅哥',3)
--
Create view se
As
Select sno,,number as’库存数量’
end
go
--出库时更新仓库库存信息
create procedure lj_cool_cout(@sno char(8),@sname char(20),@kind char(12),@number int)
as
declare @a char(8),@b int
select @a=sno from lj_cool where sno=@sno
if (@a is null)
begin
insert into lj_cool(sno,sname,kind,number) values(@sno,@sname,@kind,@number)
end
else begin
update lj_cool
set number=number+@number where sno=@a
--显示库存信息表
create table lj_cool
(
sno char(8),
number int,
sname char(20),
kind char(12),
)
go
--显示进库信息表，同时进行进库操作
create table lj_cin
(
sno char(8),
insert into lj_cin (sname,kind,sno,number ) values('封口器','利器','003',9)
insert into lj_cin (sname,kind,sno,number ) values('螺丝','机器','001',4)
go
select * from lj_cin
insert into lj_cin (sname,kind,sno,number ) values('哗哗','倒萨','4541',2)
insert into lj_cin (sname,kind,sno,number ) values('阿斯顿','爱的','225',1)
insert into lj_cin (sname,kind,sno,number ) values('螺丝','机器','001',9)
insert into lj_cin (sname,kind,sno,number ) values('老虎钳','工具','002',4)
insert into lj_cin (sname,kind,sno,number ) values('痘痘','杀人','005',3)
insert into lj_cin (sname,kind,sno,number ) values('搋子','速度','0581',5)
)
go
--连接单次进库数量为3的库存数据
select lj_cool.sno as'编号',lj_cool.sname'名字',lj_cool.kind as'种类',lj_cool.number as'库存数量'
from lj_cin,lj_cool
where lj_cin.sno=lj_cool.sno and lj_cin.number=3
go
--进库触发器
create trigger insert_cin
on lj_cin
for insert
as
declare @a char(8),@b char(20),@c char(12),@d int
select @a=sno,@b=sname,@c=kind,@d=number from inserted
if(@a=0)
begin
print @c
print '库存空了，速度进货'
end
go
--当lj_cin表出现进库错误，要删除进库数据时候创建触发器
create trigger lj_cin_woory
on lj_cin
for delete
as
declare @a char(8),@d int
select @a=sno,@d=number from deleted
update lj_cool
set number=number-@d where sno=@a
go
--当lj_cout出库错误，删除出库某条数据时创建触发器