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