On Monday, August 30, 2010 07:20:14 Maurice Gittens wrote: > Hi, > > Assume tablex, tabley and tablez are correctly populated in my database. > > My purpose is to enforce referential integrity between a column in the > tablex (the child) > and a column in tablez (the parent). > > Since normal foreign keys do not give me this functionality, I decide > to write a trigger. > My trigger function looks something like: > > CREATE OR REPLACE FUNCTION trigger_on_tablex() RETURNS trigger AS $$ > BEGIN > PERFORM 1 FROM > tablex AS tab_x > INNER JOIN tabley AS tab_y ON tab_x.gp = tab_y.id > INNER JOIN tablez AS tab_z ON tab_y.ml = tab_z.id > WHERE > tab_x.name = tab_z.name; > > IF NOT FOUND THEN > RAISE EXCEPTION 'constraint violated '; > END IF; > END;$$ LANGUAGE plpgsql; > > CREATE TRIGGER mytrigger > AFTER INSERT ON tablex FOR EACH STATEMENT EXECUTE PROCEDURE > trigger_on_tablex(); > > My problem is that no matter what I insert into tablex, the exception > is always raised. > > So, it seems that even though my trigger is defined as AFTER INSERT > FOR EACH STATEMENT, the inserted row > does not appear to be included in the join. > > So, now to my question: Should, as a matter of principle, statement > level triggers not "see" rows recently inserted into the tablex? > > Thanks, > Maurice They do "see" those rows. Are you sure that the inner join with tab_Y is not causing the problem? Just a guess... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 terry@xxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
