create table xiao --销售表 (id int identity,h_id varchar(50),h_name varchar(50),h_num int, h_money money,h_time datetime default getdate(),h_c varchar(50))
create table ku --库存表 (id int identity,h_id varchar(50) not null primary key,h_name varchar(50),h_num int, h_money money,h_time datetime default getdate(),h_c varchar(50))create table jin --进货表 (id int identity,h_id varchar,h_name varchar(50),h_num int, h_money money,h_time datetime default getdate(),h_c varchar(50)) insert into xiao(h_id,h_name,h_num,h_money,h_c) values(001,'黄瓜',20,5,'522') insert into ku(h_id,h_name,h_num,h_money,h_c) values(008,'黄瓜',40,50,'522') insert into jin(h_id,h_name,h_num,h_money,h_c) values(002,'鸡蛋',100,2.5,'522')select * from xiao select * from ku select * from jincreate trigger xiao_ku_insert --销售从库存提取 on xiao for insert as if not exists(select * from inserted where h_id in (select h_id from ku)) begin print '没货怎么提?' rollback transaction end else begin declare @num int declare @num2 int set @num=(select h_num from inserted) set @num2=(select h_num from ku where h_id=(select h_id from inserted)) if (@num<=@num) begin update ku set h_num=@num2-@num where h_id=(select h_id from inserted) end else begin print '货物不够请冲值' rollback transaction end end create trigger jin_ku_insert --进货到库存 on jin for insert as declare @num int declare @num2 int set @num=(select h_num from inserted) set @num2=(select h_num from ku where h_id=(select h_id from inserted)) if exists(select * from inserted where h_id in (select h_id from ku)) begin update ku set h_num=@num+@num2 where ku.h_id=(select h_id from inserted) end else begin insert into ku(h_id,h_name,h_num,h_money,h_c) select h_id,h_name,h_num,h_money,h_c from inserted endselect * from xiao select * from ku select * from jinidentity_insert on drop table xiao,ku,jin--注:以上表有个弊端:h_id和h_name都能表示列,进行插入,但是当第二次插入时,如果h_id和h_name不一样也能插, --这样就会产生错误。解决方法:最好用h_id和h_name中的一个,就能起到表示物品的作用。所插值时要小心.这是我以前做的练习。。你加字段就可以了。我做的很简单!
[email protected]
(id int identity,h_id varchar(50),h_name varchar(50),h_num int,
h_money money,h_time datetime default getdate(),h_c varchar(50))
create table ku --库存表
(id int identity,h_id varchar(50) not null primary key,h_name varchar(50),h_num int,
h_money money,h_time datetime default getdate(),h_c varchar(50))create table jin --进货表
(id int identity,h_id varchar,h_name varchar(50),h_num int,
h_money money,h_time datetime default getdate(),h_c varchar(50))
insert into xiao(h_id,h_name,h_num,h_money,h_c) values(001,'黄瓜',20,5,'522')
insert into ku(h_id,h_name,h_num,h_money,h_c) values(008,'黄瓜',40,50,'522')
insert into jin(h_id,h_name,h_num,h_money,h_c) values(002,'鸡蛋',100,2.5,'522')select * from xiao
select * from ku
select * from jincreate trigger xiao_ku_insert --销售从库存提取
on xiao for insert
as
if not exists(select * from inserted where h_id in (select h_id from ku))
begin
print '没货怎么提?'
rollback transaction
end
else
begin
declare @num int
declare @num2 int
set @num=(select h_num from inserted)
set @num2=(select h_num from ku where h_id=(select h_id from inserted))
if (@num<=@num)
begin
update ku set h_num=@num2-@num where h_id=(select h_id from inserted)
end
else
begin
print '货物不够请冲值'
rollback transaction
end
end
create trigger jin_ku_insert --进货到库存
on jin for insert
as
declare @num int
declare @num2 int
set @num=(select h_num from inserted)
set @num2=(select h_num from ku where h_id=(select h_id from inserted))
if exists(select * from inserted where h_id in (select h_id from ku))
begin
update ku set h_num=@num+@num2
where ku.h_id=(select h_id from inserted)
end
else
begin
insert into ku(h_id,h_name,h_num,h_money,h_c) select h_id,h_name,h_num,h_money,h_c from inserted
endselect * from xiao
select * from ku
select * from jinidentity_insert on
drop table xiao,ku,jin--注:以上表有个弊端:h_id和h_name都能表示列,进行插入,但是当第二次插入时,如果h_id和h_name不一样也能插,
--这样就会产生错误。解决方法:最好用h_id和h_name中的一个,就能起到表示物品的作用。所插值时要小心.这是我以前做的练习。。你加字段就可以了。我做的很简单!