Thread: Question on trigger data visibility

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

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

Terry Lee Tucker <terry@chosen-ones.org> writes:
> On Monday, August 30, 2010 07:20:14 Maurice Gittens wrote:
>> 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?

> They do "see" those rows. Are you sure that the inner join with tab_Y is not
> causing the problem? Just a guess...

It also seems worth pointing out that this trigger would hardly ensure
referential integrity.  As quoted, it would succeed so long as there is
at least one tablex row that is properly referencing some tablez row.
Surely you want to require that they *all* do.

            regards, tom lane