rmokhtar
asked on
Update and commit using trigger
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.Q Filled;
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;
/
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.Q
QSelling := sell_rec.Quantity-sell_rec
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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