Re: Inserting Data

On Thu, Aug 24, 2006 at 09:20:11AM -0700, Bob Pawley wrote:
> In attempting to use the IF expression (below) I receive an error
> message stating the return includes two or more rows. This seems
> to make sense since I am asking if one condition exists
> (p_id device_number = library device_number then the return should
> be all the rows that equal 'mon' (or 'end').
>
> Is there a method of modifying the IF expression to look only at
> the row in which device_number equals device_number. (I've tried
> changing AND to WHERE with no affect.

My previous message requested that you show some insert and update
commands that you'll be executing after the initial state is set
up, along with a description of what effect those statements should
have on other tables. Could you post that, please? Without an
understanding of what you're trying to achieve we have to guess
based on what the misbehaving trigger function looks like. Rather
than see what doesn't work, let's start with the requirements and
build something that does.

As for problems with the function....

> if new.device_number = library.devices.device_number
> and library.devices.type_ = 'end'

The p_id.devices table in our example doesn't have a device_number
column. Should it? Also, if the above statement runs at all then
I'd guess you have add_missing_from turned on, which can cause
unexpected query behavior. Maybe this is what you mean:

if exists (select 1 from library.devices
where device_number = new.device_number and type_ = 'end')
then

The original expression implies that a query should be made; the
rewritten version is explicit about what that query should be. But
debugging the function is premature: let's see the requirements and
then figure out how to implement them.

--
Michael Fuhr