Re: Trigger Question

From: Grue <grue_at_stopspam.hotmail.com>
Date: 1998/02/12
Message-ID: <34e368ec.26882990_at_128.158.254.10>#1/1

You don't need to do an update on the table from within the trigger. Since you're already inserting or updating, just set the item_price and vendor_id. In your trigger, select the required values from table_2, then assign those values to :new.item_price and :new.vendor_id.

Something like this:

     SELECT item_price, vendor_id FROM table_2
     INTO price, vendor
     WHERE item_number = :new.item_number;

     :new.item_price := price;
     :new.vendor_id := vendor;

Note that price and vendor are local bind variables.

This should solve your mutating table problems, and is much cleaner, too.

On Thu, 12 Feb 1998 14:46:54 -0600, Ryan Rucker <rxruck2_at_uswest.com> wrote:

>Hello,
>
>I'm having a problem setting up an AFTER INSERT or UPDATE trigger and
>need some help.
>
>I have two tables that have some columns in common, sort of like this:
>
>table_1 table_2
>----------------- --------------------
>item_number item_number not null
>item_price item_price not
>null
>vendor_id vendor_id not null
>
>I'm trying to create a trigger that will fire if a person updates
>item_number on table_1 and doesn't include the item_price or the
>vendor_id. When this happens, I'd like the trigger to get the
>item_price and vendor_id from table_2 and then set item_price and
>vendor_price in table_1 to those values. Make sense?
>
>I keep running into mutating table errors, I think because I'm trying to
>update the same table I'm triggering from. My trigger, logically
>anyway, would look something like this:
>
>create or replace trigger trigger_1
>after insert or update of item_number on table_1
>for each row
>begin
> update table_1 t1 set (t1.item_price, t1.vendor_id) =
> (select t2.item_price, t2.vendor_id
> from table_2 t2
> where t1.item_number = t2.item_number)
> where t1.item_number = :new.item_number
>end;
>/
>
>At least that's what I'm trying to do. Even though I'm not updating
>item_number with this trigger, I still get a mutating table error, I
>think due to the update on the table in general.
>
>Does anyone know of a different method to accomplish this, because what
>I'm trying above doesn't work. The only restriction that I'm working
>with is that it has to be an AFTER trigger, it can't be a BEFORE
>trigger.
>
>Thanks,
>
>Ryan
>

Grue

---

Please remove "stopspam." from address to reply. Received on Thu Feb 12 1998 - 00:00:00 CET