Postgres Trigger - Working with 2 different tables

Question

I have 2 tables:

Books - isbn (PK), title, qty-in-stock
Orderlist - ordernum (PK), isbn, quantity

My goal is to insert a record into orderlist and then, if matching isbns, add the quantity to qty-in-stock

My function and trigger is not correct -

CREATE OR REPLACE FUNCTION books_upd() RETURNS trigger as $bookupd$
BEGIN 
--IF   THE ISBN MATCHES BETWEEN BOOKS AND ORDERLIST
-- ADD THE EXISTING QTY_IN_STOCK (BOOKS)  TO QUANTITY (ORDERLIST)
QTY_IN_STOCK:=QTY_IN_STOCK+QUANTITY;
--END IF
RETURN NEW;
$bookupd$ LANGUAGE plpgsql;

CREATE TRIGGER books_upd
BEFORE INSERT OR UPDATE on orderlist
FOR EACH ROW
EXECUTE PROCEDURE books_upd();

Can anyone help?

klin · Accepted Answer · 2015-11-30 19:44:59Z

1

The trigger function is quite complicated because of different cases of insert and update.

When a user updates the order, then the function should:

add to qty_in_stock the difference between new and old quantities if isbns are the same or
subtract old quantity from qty_in_stock of one book and add new quantity to qty_in_stock of another book when isbns are different.

Additionally, the function should reject changes if the given isbn does not exist in books.

CREATE OR REPLACE FUNCTION books_upd() 
RETURNS trigger as $bookupd$
DECLARE
    v_quantity int;
BEGIN
    v_quantity = NEW.quantity;
    IF TG_OP = 'UPDATE' THEN
        IF OLD.isbn != NEW.isbn THEN
            UPDATE books
            SET qty_in_stock = qty_in_stock- OLD.quantity
            WHERE isbn = OLD.isbn;
        ELSE
            v_quantity = NEW.quantity- OLD.quantity;
        END IF;
    END IF;

    UPDATE books
    SET qty_in_stock = qty_in_stock+ v_quantity
    WHERE isbn = NEW.isbn;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Uknown isbn';
    END IF;
    RETURN NEW;
END;
$bookupd$ LANGUAGE plpgsql;

2 Comments

knight Over a year ago

Actually, I made this too complicated. Sorry! We would only have an insert case. Also, isbn from orderlist is foreign key to isbn in books. So, it will always be found. I don't believe we need your second bullet point. Your answer was very helpful! Given these simplifiers, what is a new coding?

knight Over a year ago

An additional challenge as business rules are developed- I found out that SET qty_in_stock = qty_in_stock+ NEW.quantity

Collectives™ on Stack Overflow

Postgres Trigger - Working with 2 different tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related