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