I have the following scenario.
Every row in collections table has an items_total column.
This items_total should be updated (incremented/decremented) every time a new row is added to the items table, if the items.collection_id column equals the id of the row in collections table.
It seems that one of the ways to achieve this should be via triggers (I am not sure if that is the better way to do things).
I put together the following code, but the counter in collection's row items_total column doesn't update when I add new items to the items table.
CREATE OR REPLACE FUNCTION update_items_total()
RETURNS trigger AS $$
BEGIN
UPDATE public.collections
SET items_total = items_total + 1
WHERE id = NEW.collection_id;
END;
$$ LANGUAGE plpgsql;
-- Create a trigger to increment items_total after insert on public.items
CREATE TRIGGER increment_items_total
AFTER INSERT ON public.items
FOR EACH ROW
EXECUTE PROCEDURE update_items_total();
Could you please suggest how should I modify the query in order to achieve the goal: maintaining the total count of items with the matching collection_id within the collections row if the collection row id matches the items row collection_id?
While you could use a trigger like this, have you considered creating a View instead?
The problem with adding this column is that you’ll run the risk of it eventually getting out of sync. For instance - this trigger will update when a new item is added to a collection - but what if an item is removed? What if an item is moved between collections?
You could meticulously take care of all these scenarios with seperate triggers for INSERT, UPDATE answer DELETE statements - but still you run the risk of these breaking down the road.
It is usually safer to keep the data seperate and compute the item count at query time using a view. It can be as simple as:
CREATE OR REPLACE VIEW collection_with_count AS
SELECT c.*, items.items
FROM public.collections AS c
LEFT JOIN (
SELECT collection_id, COUNT(*) AS items
FROM public.items
GROUP BY collection_id
) AS items ON items.collection_id = c.id
This structure will prevent having duplicate data that can get out of sync eventually