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_stock and 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();
Where inv_groups_vw is a VIEW with the predefined SELECT to return all the item groups inside inv_items.
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?
WHEREwhich is a little more complexe than a simple FK check...BEFOREtrigger - it fires before the row is inserted which cannot appear in the view yet. Change it toAFTERand (after considering @CraigRinger's suggestion) you are done.