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 jin
create 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
end
select * from xiao
select * from ku
select * from jin