Database Reference
In-Depth Information
Managing related data with triggers
Server programming can also mean setting up automated actions (triggers), so that
some operations in the database cause some other things to happen as well. For ex-
ample, you can set up a process where making an offer on some items is automatic-
ally reserved to them in the stock table.
So let's create a fruit stock table:
CREATE TABLE fruits_in_stock (
name text PRIMARY KEY,
in_stock integer NOT NULL,
reserved integer NOT NULL DEFAULT 0,
CHECK (in_stock between 0 and 1000 ),
CHECK (reserved <= in_stock)
);
The
CHECK
constraints make sure that some basic rules are followed: you can't have
more than
1000
fruits in stock (they'll probably go bad), you can't have negative stock,
and you can't reserve more than what you have.
CREATE TABLE fruit_offer (
offer_id serial PRIMARY KEY,
recipient_name text,
offer_date timestamp default
current_timestamp,
fruit_name text REFERENCES fruits_in_stock,
offered_amount integer
);
The
offer
table has an ID for the offer (so you can distinguish between offers later),
recipient, date, offered fruit name, and offered amount.
For automating the reservation management, you first need a
TRIGGER
function,
which implements the management logic:
