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(at)chosen-ones(dot)org