SQL Trigger Function, Update Lowest Bid

I have a postgres db with the following tables:

CREATE TABLE posters
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    contact_info VARCHAR(1000)
);

CREATE TABLE bidders
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    contact_info VARCHAR(100)
);

CREATE TABLE jobs
(
    id SERIAL PRIMARY KEY,
    poster_id INTEGER REFERENCES posters(id),
    description VARCHAR(100),
    requirements VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    exp_date DATE NOT NULL,
    active BOOLEAN NOT NULL
);

CREATE TABLE bids
(
    id SERIAL PRIMARY KEY,
    job_id INTEGER REFERENCES jobs(id),
    bidder_id INTEGER REFERENCES bidders(id),
    bid_amount INTEGER NOT NULL
);

CREATE TABLE lowest_bids
(
    id SERIAL PRIMARY KEY,
    bidder_id INTEGER REFERENCES bidders(id),
    lowest_bid INTEGER,
    num_bids INTEGER,
    job_id INTEGER REFERENCES jobs(id) UNIQUE
);

And the following trigger function:

CREATE OR REPLACE FUNCTION update_lowest_bids
()
RETURNS TRIGGER AS $$
DECLARE
lowest_bid_record RECORD;
BEGIN
SELECT COUNT(\*), MIN(bid_amount), job_id, bidder_id
INTO lowest_bid_record
FROM bids
WHERE job_id = COALESCE(NEW.job_id, OLD.job_id)
GROUP BY job_id;

    INSERT INTO lowest_bids
        (num_bids, lowest_bid, job_id, bidder_id)
    VALUES
        (lowest_bid_record.count, lowest_bid_record.min, lowest_bid_record.job_id, lowest_bid_record.bidder_id)
    ON CONFLICT
    (job_id) DO
    UPDATE
    SET num_bids = excluded.num_bids,
        lowest_bid = excluded.lowest_bid;
    
    RETURN NULL;

END;
$$ LANGUAGE plpgsql;

and the following trigger:

CREATE TRIGGER bids_update_trigger 
AFTER INSERT OR UPDATE OR DELETE ON bids 
FOR EACH ROW 
    EXECUTE FUNCTION update_lowest_bids ();

For some reason the lowest_bids table is not being updated with a lower bid_amount on the same job_id. I’m not experienced with SQL functions. Does anyone know what part of the trigger function is the problem, or what I’m missing?

Thank you!

You need to sign in to view this answers

Related Post