Re: Please help with this trigger

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 16 Nov 1999 14:27:04 +0100
Message-ID: <80rm5m$93d$1@oceanite.cybercable.fr>

Andy <abruskoNOabSPAM_at_binney-smith.com.invalid> a écrit dans le message : 1415c574.fee33839_at_usw-ex0102-015.remarq.com...
> 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;

:new doesn't exist in case of delete, use :old delete from frt.supplier where abv = :old.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;

You have to restrict your update to the old row: update frt.supplier set abv = :new.abv,

                        supplier = :new.customer_vendor,
                        customer = :new.customer_vendor_no,
                        code = :new.code,
                        division = :new.division

where abv = :old.abv;
(assuming that abv is your primary key).

> end if;
> end;
>

--
Have a nice day
Michel Received on Tue Nov 16 1999 - 07:27:04 CST