Apply a constraint on other rows in the same table

advertisements

I have a table with bid to an auction and every licitation bid to some auction should be higher than every other regarding that same auction. How can this be done? As I don't know a way to make a constraint work I thought about using a trigger but didn't find any with a similar objective (I'm really new to sql).

My table creation SQL:

CREATE TABLE bid(
  id_auction NUMBER(10) NOT NULL,
  username  VARCHAR(20) NOT NULL,
  amount    FLOAT   NOT NULL,
  b_date DATE NOT NULL,
  CONSTRAINT pk_bid PRIMARY KEY (id_auction, username, amount),
  FOREING KEY (username) REFERENCES user(username),
  FOREIGN KEY (id_auction) REFERENCES auction(id_auction)

);

My incomplete trigger:

CREATE OR REPLACE TRIGGER high_bid_trigger
BEFORE INSERT
ON bid
DECLARE
    highest_bid NUMBER;
BEGIN
    highest_bid := (SELECT min(amount)
                    FROM bid
                    WHERE username = :NEW.username
                    AND id_aution = :NEW.id_auction)
    if highest_bid < :NEW:bid

I'm using an Oracle database.


You can do this using a trigger, I think. When you get data from the same table in a trigger, you can have a problem with mutating triggers. These are hard to fix.

I don't know if it is feasible, but you could solve this problem by storing the increment rather than the absolute value. Then, you can guarantee that the increment is greater than 0:

constraint chk_t_increment check (increment > 0);

This is messy when you want to query the values, however.

Another option would be to use an after insert trigger to maintain the data in another table. So, the auction table would have a column for the current highest bid.

You can then use this value in a before insert trigger, where the old value would be used.

This type of problem is an example of why I like to wrap data modification steps into stored procedures, rather than handling them through direct calls to update/insert/delete. A stored procedure would give the flexibility to do what you want to do rather easily with no triggers involved.