1

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?

  • I don't understand why you have the trigger in the first place. Looks like you are trying to implement a foreign key check there, so why not just use a foreign key constraint? – a_horse_with_no_name Jul 2 '14 at 20:24
  • @a_horse_with_no_name I can have an FK on a view? – Yanick Rochon Jul 2 '14 at 20:28
  • No, but the ids you are looking for have to be stored in some table, and you can have a FK to that. – a_horse_with_no_name Jul 2 '14 at 20:42
  • @a_horse_with_no_name, no that won't work. The constraint is based on a WHERE which is a little more complexe than a simple FK check... – Yanick Rochon Jul 2 '14 at 20:44
  • 2
    I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering @CraigRinger's suggestion) you are done. – dezso Jul 3 '14 at 8:43
2

I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering Craig's suggestion) you are done.

  • I'll give it a try. Meanwhile, I disabled the trigger while I test the interfaces. There's already validation inside the application and the trigger is simply as a second layer validation... – Yanick Rochon Jul 3 '14 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.