Database Reference
In-Depth Information
CREATE OR REPLACE FUNCTION
reserve_stock_on_offer () RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE fruits_in_stock
SET reserved = reserved +
NEW.offered_amount
WHERE name = NEW.fruit_name;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE fruits_in_stock
SET reserved = reserved -
OLD.offered_amount
+
NEW.offered_amount
WHERE name = NEW.fruit_name;
ELSIF TG_OP = 'DELETE' THEN
UPDATE fruits_in_stock
SET reserved = reserved -
OLD.offered_amount
WHERE name = OLD.fruit_name;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
You have to tell PostgreSQL to call this function each and every time the offer row is
changed:
CREATE TRIGGER
manage_reserve_stock_on_offer_change
AFTER INSERT OR UPDATE OR DELETE ON fruit_offer
FOR EACH ROW EXECUTE PROCEDURE
reserve_stock_on_offer();
After this we are ready to test the functionality. First, we will add some fruit to our
stock:
