Re: Inserting Data

On Mon, Aug 21, 2006 at 08:27:58AM -0700, Bob Pawley wrote:
> Yes - Multiple rows of the same data are created in each secondary table.
>
> I have two triggers that are identical in format although handling
> different tables. One is triggeres after insert and with this there is no
> multiplying factor.
>
> The other is triggered after an update.

The insert-vs-update distinction might be a red herring; the
difference in behavior might be a result of the queries run inside
the trigger functions. Or maybe the statements executed by the
update trigger are firing additional triggers. Without more
information we can only guess.

> Both triggers use NEW.* in the same manner. However, the trigger after
> update gives multiple results of the same information.

How are the triggers using NEW? In your original message the
function didn't use NEW at all.

> Is there any way around this problem? Is there perhaps a method restricting
> the trigger to an update to a particular column rather than the table as a
> whole?

Do you mean "particular row" instead of "particular column"?

If you're executing INSERT ... SELECT statements from inside a
trigger function as in your original message, then the restriction
on the SELECT determines how many rows are inserted. It's possible
that those inserts are causing additional triggers to fire. Have
you added any RAISE statements to the trigger functions to see when
they're being called?

Could you post a simple, self-contained example that exhibits both
the desired and undesired behavior? That is, all SQL statements
that somebody could load into an empty database to create and
populate the tables, create the triggers, and perform whatever
actions are necessary to elicit both behaviors.

--
Michael Fuhr