Question on trigger data visibility

[Maurice Gittens <mainmanmauricio@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general