Given a table layout that looks like this
inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty int ... inv_items_group =============== group_id bigint (inv_items) item_id bigint (inv_items)
where an item may be a "stock item" or a "group item" (with compound items...) I have a trigger that checks before insert / update on each
inv_items_group to make sure that some conditions are met; for example, a stock item cannot be a group item.
The problem is that, in my program, I run a batch of inserts inside a transaction; first I insert into
inv_items, then into one of the other table. Unfortunately, the
SELECT inside my trigger function does not find any item that I just inserted within the transaction, thus always fails.
For example, here is the group item check trigger
CREATE FUNCTION inv_check_set_group_items() RETURNS trigger AS $INV_GRP_SET$ DECLARE found_item bigint; BEGIN SELECT id INTO found_item FROM inv_groups_vw WHERE id = NEW.group_id; IF found_item IS NULL THEN RAISE EXCEPTION 'Invalid group item'; END IF; RETURN NEW; END; $INV_GRP_SET$ LANGUAGE plpgsql; CREATE TRIGGER inv_check_set_group_items BEFORE INSERT OR UPDATE ON inv_items_group FOR EACH ROW EXECUTE PROCEDURE inv_check_set_group_items();
inv_groups_vw is a
VIEW with the predefined
SELECT to return all the item groups inside
Is the problem with the
VIEW or the
FUNCTION? What can I do to fix this and be able to select rows of newly inserted items within the transaction? ... am I missing anything else?
Could it be that my condition
IF found_item IS NULL THEN is flawed?