Trigger Question

From: Ryan Rucker <rxruck2_at_uswest.com>
Date: 1998/02/12
Message-ID: <34E35FBE.F4DE2A7D_at_uswest.com>#1/1

Hello,

[Quoted] 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 Received on Thu Feb 12 1998 - 00:00:00 CET