Re: Question on trigger data visibility

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@chosen-ones.org