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

As I mentioned in my previous post the short answer is ignorance.  However, by
reading the documentation and testing I have learned that unfortunately AFTER
STATEMENT is not available in v7.3.6 anyway.  I am hoping to upgrade to v8
when it is released but we are currently facing a project deadline and I can't
wait.  Not to mention that v8 may 'break' some of the software that we have
written.

What is really confusing to me is why the trigger works when a single line is
COPYed into the inital table and fails when multiple rows are COPYed.  Any
ideas on why this happens and suggested work arounds would really be appreciated.

Kind Regards,
Keith

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