"Keith Worthington" <keithw@narrowpathinc.com> writes:
> I have written a function that moves data from a load table
> (data_transfer.tbl_sales_order_line_item) to two data tables
> (sales_order.tbl_line_item & sales_order.tbl_item_description) the second of
> which is dependent on the first. When I run the function manually (create it
> as a function returning an integer and execute it after loading the data witht
> the COPY command) it works fine. When I convert it to a TRIGGER and COPY data
> to the load table I get an error that says a foreign key constraint is being
> violated.

Why are you iterating over the whole table in a FOR EACH ROW trigger?
At best that's exceedingly inefficient. If you don't want to change
the function then it should probably be an AFTER STATEMENT trigger.

The example works with no error for me in 8.0, but in 7.4 I do get a
failure. I think the difference has to do with the delayed firing of
AFTER triggers in 7.4, but I'm not entirely sure why that's affecting
anything.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

> "Keith Worthington" <keithw@narrowpathinc.com> writes:
> > I have written a function that moves data from a load table
> > (data_transfer.tbl_sales_order_line_item) to two data tables
> > (sales_order.tbl_line_item & sales_order.tbl_item_description) the second of
> > which is dependent on the first. When I run the function manually (create it
> > as a function returning an integer and execute it after loading the data witht
> > the COPY command) it works fine. When I convert it to a TRIGGER and COPY data
> > to the load table I get an error that says a foreign key constraint is being
> > violated.
>
> Why are you iterating over the whole table in a FOR EACH ROW trigger?
> At best that's exceedingly inefficient. If you don't want to change
> the function then it should probably be an AFTER STATEMENT trigger.
>
> The example works with no error for me in 8.0, but in 7.4 I do get a
> failure. I think the difference has to do with the delayed firing of
> AFTER triggers in 7.4, but I'm not entirely sure why that's affecting
> anything.
>
> regards, tom lane

Hi Tom,

Thanks for the reply. The brutally honest answer to your question is
ignorance. As you can see from my earlier post I am using COPY to place one
or more records into the transfer table. I would prefer the trigger fire just
once after all the data has been loaded but I have no idea how to make it do
that. I can certainly understand why it would be more efficient.

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster