Update and commit using trigger

rmokhtar
rmokhtar used Ask the Experts™
on
Hi,

I have this project I'm working on for school. It's primitive, so bear with me. I have an Orderbook with this structure:

SQL> desc orderbook;
 Name                                                Null?         Type
 -----------------------------------------  --------       ----------------------------
 MARKETID                                        NOT NULL  NUMBER(6)
 CLIENTID                                                             NUMBER(6)
 CLIENTSEQID                                                      NUMBER(6)
 PRICE                                               NOT NULL  FLOAT(6)
 QUANTITY                                       NOT NULL  NUMBER(5)
 MARKETTIME                                                     TIMESTAMP(6)
 CLIENTTIME                                                        TIMESTAMP(6)
 QFILLED                                                             NUMBER
 FILLED                                                                NUMBER

there are two kinds of entries:

1) Price < 0 that's a sell
2) Price > 0 that's a buy

I'm attempting to update the table as I go. i.e. if I match a sell with a buy, I want the Filled value to equal 1. I also don't want the trigger to enter the if statement again if Filled=1 OR QFilled >= Quantity. However, the trigger does it anyway. It enters the if statement as if nothing's been updated. I attempted a commit, but apparently commits aren't allowed in triggers except autonomous. Please help.

here's my trigger:

create or replace trigger match
after insert on OrderBook
declare
MinPrice Orderbook.Price%TYPE;
TempPrice Orderbook.Price%TYPE;
BDone Orderbook.Filled%TYPE;
SDone Orderbook.Filled%TYPE;
QNeeded Orderbook.QFilled%TYPE;
QSelling Orderbook.QFilled%TYPE;
cursor buy_cur is
select * from OrderBook where Filled=0 AND Price > 0 order by Price desc, MarketTime FOR UPDATE NOWAIT;
cursor sell_cur is
select * from OrderBook where Filled=0 AND Price < 0 order by Price desc, MarketTime FOR UPDATE NOWAIT;
begin
BDone := 0;
SDone := 0;

FOR sell_rec IN sell_cur LOOP
      FOR buy_rec IN buy_cur LOOP
            if buy_rec.ClientID != sell_rec.ClientID AND buy_rec.QFilled < buy_rec.Quantity AND sell_rec.QFilled < sell_rec.Quantity AND BDone = 0 AND SDone = 0 then
                  
                  UPDATE OrderBook
                  SET Filled = 1
                  WHERE QFilled=Quantity;
                  commit;
                  
                  QNeeded := buy_rec.Quantity-buy_rec.QFilled;
                  QSelling := sell_rec.Quantity-sell_rec.QFilled;
                  
                                    
                  if buy_rec.Price < -sell_rec.Price OR buy_rec.QFilled = buy_rec.Quantity OR  sell_rec.QFilled = sell_rec.Quantity then
                        BDone := 1;
                        SDone := 1;
                        exit;
                  
                  elsif buy_rec.Price > -sell_rec.Price then
                  --compare timestamp if sell before buy, set at sell else set at buy
                        if buy_rec.MarketTime < sell_rec.MarketTime then
                              TempPrice := buy_rec.Price;
                        else
                              TempPrice := -sell_rec.Price;
                        end if;
                        
                        if QNeeded=QSelling then
                              
                              insert into MatchBook(SellerMarketID, BuyerMarketID, MatchingPrice, QFilled)
                              values (sell_rec.MarketID, buy_rec.MarketID, TempPrice, QNeeded);
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QNeeded
                              WHERE MarketID=buy_rec.MarketID;
                                          
                              UPDATE OrderBook
                              SET QFilled = QFilled+QSelling
                              WHERE MarketID=sell_rec.MarketID;
                              
                        elsif QNeeded < QSelling then

                              insert into MatchBook(SellerMarketID, BuyerMarketID, MatchingPrice, QFilled)
                              values (sell_rec.MarketID, buy_rec.MarketID, TempPrice, QNeeded);
                              
                              --insert into orderbook leftover sell quantity
                              UPDATE OrderBook
                              SET QFilled = QFilled+QNeeded
                              WHERE MarketID=buy_rec.MarketID;
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QNeeded
                              WHERE MarketID=sell_rec.MarketID;
                              
                        elsif QNeeded > QSelling then
                              insert into MatchBook(SellerMarketID, BuyerMarketID, MatchingPrice, QFilled)
                              values (sell_rec.MarketID, buy_rec.MarketID, TempPrice, QSelling);
                              
                              --insert into orderbook leftover buy quantity
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QSelling
                              WHERE MarketID=buy_rec.MarketID;
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QSelling
                              WHERE MarketID=sell_rec.MarketID;
                        end if;
                        
                        
                        --Done := 1;
                        --exit;
                  elsif buy_rec.Price = -sell_rec.Price then
                        
                              
                        if QNeeded=QSelling then
                              
                              insert into MatchBook(SellerMarketID, BuyerMarketID, MatchingPrice, QFilled)
                              values (sell_rec.MarketID, buy_rec.MarketID, buy_rec.Price , QNeeded);
                              UPDATE OrderBook
                              SET QFilled = QFilled+QNeeded
                              WHERE MarketID=buy_rec.MarketID;
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QSelling
                              WHERE MarketID=sell_rec.MarketID;
                                                            
                        elsif QNeeded < QSelling then
                              
                              insert into MatchBook(SellerMarketID, BuyerMarketID, MatchingPrice, QFilled)
                              values (sell_rec.MarketID, buy_rec.MarketID, buy_rec.Price , QNeeded);
                              
                               --insert into orderbook leftover sell quantity
                              UPDATE OrderBook
                              SET QFilled = QFilled+QNeeded
                              WHERE MarketID=buy_rec.MarketID;
                                          
                              UPDATE OrderBook
                              SET QFilled = QFilled+QNeeded
                              WHERE MarketID=sell_rec.MarketID;
                              
                        elsif QNeeded > QSelling then
                              insert into MatchBook(SellerMarketID, BuyerMarketID, MatchingPrice, QFilled)
                              values (sell_rec.MarketID, buy_rec.MarketID, buy_rec.Price , QSelling);
                              
                              --insert into orderbook leftover buy quantity
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QSelling
                              WHERE MarketID=buy_rec.MarketID;
                              
                              UPDATE OrderBook
                              SET QFilled = QFilled+QSelling
                              WHERE MarketID=sell_rec.MarketID;
                        
                        end if;      
                        --Done := 1;
                        
                        if buy_rec.QFilled = buy_rec.Quantity then
                              BDone := 1;
                        end if;
                        
                        if sell_rec.QFilled = sell_rec.Quantity then
                              SDone := 1;
                        end if;
                        
                        
                        --exit;                  
                  end if;--Price
                  
            
            end if;--buy_rec.MarketID != sell_rec.MarketID
            
            --if SDone = 1 then exit; end if;      
            exit when buy_cur%NOTFOUND;
      end loop;
      exit when sell_cur%NOTFOUND;
end loop;
end;
/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Architect
Commented:
From the code it is hard to understand what you are trying to do here. A few suggestions:
- Triggers have limitations, you cannot perform every programmatic logic using a trigger.
- Try to simplify things by using a better data model. I.e. probably split the table to have the sell details and buy details in separate tables or to have the summary details in a separate table.

Author

Commented:
"Triggers have limitations, you cannot perform every programmatic logic using a trigger"
sujith80

That's most probably true. I would've really liked to have made this trigger work, I spent a whole day on it today and it would compromise filling all the buys or sells with their corresponding sells or buys vs. just filling one. It's not problem, I just won't use the trigger for this matching function I'm trying to implement.

Thank you,
Reem

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial