Re: Please help with this trigger

A copy of this was sent to Andy <abruskoNOabSPAM_at_binney-smith.com.invalid> (if that email address didn't require changing) On Tue, 16 Nov 1999 04:48:59 -0800, you wrote:

>Hi,
>
>I am trying to create a trigger that will keep 2 tables in sync. I
>have a master table that is maintenanced (added, changed or deleted)
>and I would like to keep a smaller version of that table in sync with
>it. If a row is added to the master, I would like to add a row to the
>other table, if one is deleted from the master, I would like to delete
>the same row from the other and any changes to the master, I would like
>to apply (if the same column exists) to the other table.
>
>Below is what I have written so far, but it is obviously not correct.
>Can someone please show me what I need to change so that it works
>correctly...I appreciate it...thanks!!! Andy
>
>create or replace trigger update_supplier
>before insert or delete or update on frt.supplier_master
>for each row
>begin
> if inserting then
> insert into frt.supplier (abv, supplier, customer, code, division)
> values (:new.abv, :new.customer_vendor, :new.customer_vendor_no,
> :new.code, :new.division);
> elsif deleting then
> delete from frt.supplier where abv = :new.abv;
> elsif updating then
> update frt.supplier set abv = :new.abv, supplier =
>:new.customer_vendor,
> customer = :new.customer_vendor_no, code = :new.code, division
>= :new.division;
> end if;
>end;
>

this sounds alot like a snapshot (maybe you could just use them).

In lieu of that here is what I see:

• the insert part looks OK.
• the delete part uses :new. there are no :new values in a delete, only old. use :old.abv;
• the update has no where clause. assuming abv is the PRIMARY key it should be:

  update frt.supplier set abv = :new.abv, suppliers = :new......    WHWER abv = :OLD.ABV;

>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 16 1999 - 07:28:50 CST