Re: Writing to dependent tables in a function

> "Keith Worthington" <keithw(at)narrowpathinc(dot)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