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
