"Cody Konior" <cody.konior@reynolds.com.au> writes:
> I've pasted the whole thing because I don't know how much is important. The
> problem is that it seems whenever we do the INSERT, the trigger causes an
> error because it says parts_purchasing table doesn't exist. Naturally... it
> does exist!
The three standard answers for this type of problem are:
1. case-folding mismatch (you quoted a mixed-case name when creating the
table and tried to reference it without quotes, or vice versa);
2. wrong schema search path;
3. obsolete cached plan.
It sounds like you already eliminated #2, and if the entire example is
shown exactly then it's not #1 either. #3 could be eliminated by
starting a fresh database session.
The real question in my mind is how this code could've ever worked at
all, though. The trigger creates a fresh update event (maybe more than
one) on its own table every time through, which will fire the same
trigger again, which means that this absolutely *should* be an infinite
loop. The only way it isn't is if the "parts_purchasing" table affected
by its UPDATE isn't the same one the trigger itself is attached to.
So I'm thinking there probably is a schema search path issue hidden
in here somewhere, but you've not given us enough information to
understand what is supposed to be happening.
regards, tom lane
