目标:
StockBid(stock_id, cust_id, flag, date, price, amount), 存储股票买入和卖出的标的,分别代表股票号,客户号,flag用来标识是买入还是卖出,标的日期,标的价格,以及标的数量。
实现一个触发器,每当插入一条记录,检查是否可以成交,成交的条件是买入价大于等于卖出价,并且最老的标的优先成交。成交时如果一方(A)的标的数量小于另一方(B),则从表中删除A的记录,保留B的记录并且修改B的标的数量。同时要求显示成交的相关信息,包括成交的买卖方、股票号、价格、数量以及时间等。
use DBforHomework
go
if (object_id('tr_stockBid_insert', 'TR') is not null)
drop trigger tr_stockBid_insert
go
---问题:触发器只存上一条被插入(或删除的)的纪录?如果之后再有插入,值就被覆盖了 ?
-- 问题:inserted表中的值实际已经插入了stockBid表,是吧?因为inserted表中的值已经是被删除了的
create trigger tr_stockBid on StockBid after insert
as
begin
--参数说明:@stock_id,@flag等是表中满足交易条件的bid对应的信息,而@instock_id,@inprice等表示插入的标对应的信息,从 inserted表取出
declare @stock_id char(10),@cust_id char(10),@flag bit,@datet datetime,@price float,@amount int
declare @instock_id char(10),@incust_id char(10),@inflag bit,@indatet datetime,@inprice float,@inamount int
--列值说明:在stockBid中,flag=1,买入标的,flag=-1,卖出标的
select @instock_id=stock_id,@incust_id=cust_id,@inflag=flag,@indatet=datet,@inprice=price,@inamount=amount from inserted
select top 1 @stock_id=stock_id,@cust_id=cust_id,@flag=flag,@datet=datet,@price=price,@amount=amount from StockBid
where flag!=@inflag and (@inflag*@inprice+price*flag)>=0 order by datet asc
if(@@rowcount=1)--如果有满足的纪录,则进行交易处理
begin
if(@amount=@inamount)
begin
delete from StockBid where stock_id=@stock_id and cust_id=@cust_id and flag=@flag and datet=@datet and price=@price and amount=@amount
delete from StockBid where stock_id=@instock_id and cust_id=@incust_id and flag=@inflag and datet=@indatet and price=@inprice and amount=@inamount
insert into TradeRecord values(@stock_id,@cust_id,@flag,@incust_id,@inflag,getdate(),@price,@amount)--插入记录
end
else
begin
if(@amount>@inamount)
begin
delete from StockBid where stock_id=@instock_id and cust_id=@incust_id and flag=@inflag and datet=@indatet and price=@inprice and amount=@inamount
update StockBid set amount=(@amount-@inamount) where cust_id=@cust_id and stock_id=@stock_id and price=@price and datet=@datet
insert into TradeRecord values(@stock_id,@cust_id,@flag,@incust_id,@inflag,getdate(),@price,@inamount)
end
else--(@amount<@inamount)
begin
delete from StockBid where stock_id=@stock_id and cust_id=@cust_id and flag=@flag and datet=@datet and price=@price and amount=@amount
delete from StockBid where stock_id=@instock_id and cust_id=@incust_id and flag=@inflag and datet=@indatet and price=@inprice and amount=@inamount
insert into TradeRecord values(@stock_id,@cust_id,@flag,@incust_id,@inflag,getdate(),@price,@amount)
--set @inamount=@inamount-@amount
insert into StockBid values(@instock_id,@incust_id,@inflag,getdate(),@inprice,@inamount-@amount)
end
end
end
else--没有满足的纪录,将新来的bid插入stockBid表
insert into StockBid values(@stock_id,@incust_id,@inflag,getdate(),@inprice,@inamount)
end