目标：
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