Re: Can this be done with a trigger?

From: Aya the Vampire Slayer <ryoko_at_gatech.rmv.this.part.edu>
Date: Wed, 6 Aug 2008 13:09:48 +0000 (UTC)
Message-ID: <g7c7qs$m0l$1@news-int2.gatech.edu>


artmerar_at_yahoo.com wa:
>On Aug 6, 3:23 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
>> Do you look for inserting and updating as well in your trigger (since you
>> only posted part of your code)? Or else your triggers fires on an update and
>> performs an insert.
>>

>The trigger fires on insert or update. The actual trigger code is
>here. Do you see anything wrong with it?

>CREATE OR REPLACE TRIGGER customer_config_trg
> INSTEAD OF UPDATE OR INSERT ON customer_config_vw
> FOR EACH ROW

>BEGIN
> IF INSERTING THEN
You also need to write your INSERT dml for the main table here before inserting into the HIST table. An INSTEAD OF trigger doesn't do it automatically like BEFORE/AFTER triggers do.

      INSERT INTO CUSTOMER_CONFIG
      VALUES (...);


> INSERT INTO customer_config_hist
> VALUES
>(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source,
>SYSDATE);
> END IF;

> IF UPDATING THEN
Same here, you also need to write the UPDATE dml for the main table here before inserting an entry into the HIST table.
      UPDATE CUSTOMER_CONFIG
         SET FOO = :new.FOO
         (...)
         where (...);


> IF :NEW.format IS NOT NULL THEN
> INSERT INTO customer_config_hist
> VALUES
>(:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, :NEW.source,
>SYSDATE);
> ELSIF :NEW.status IS NOT NULL THEN
> INSERT INTO customer_config_hist
> VALUES
>(:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, :NEW.source,
>SYSDATE);
> END IF;
> END IF;
>END;
>/
-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator
Received on Wed Aug 06 2008 - 08:09:48 CDT

Original text of this message