Re: Writing to dependent tables in a function

> "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