SQL Server触发器 练习曲 股票标的插入数据表

目标：

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