Re: Can this be done with a trigger?

From: <artmerar_at_yahoo.com>
Date: Wed, 6 Aug 2008 05:51:32 -0700 (PDT)
Message-ID: <5613db1c-e125-4207-8e37-6f0e1027cee3@a70g2000hsh.googlegroups.com>


On Aug 6, 3:23 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <artme..._at_yahoo.com> schreef in berichtnews:747980e0-ff34-4330-b48d-f3735df58bd0_at_d77g2000hsb.googlegroups.com...
>
>
>
> > On Aug 5, 4:11 pm, Aya the Vampire Slayer
> > <ry..._at_gatech.rmv.this.part.edu> wrote:
> >> artme..._at_yahoo.com wa:
>
> >> >Thanks, but I still need to resolve the ORA-07445 that I am
> >> >receiving. If you do not mind, I can post some of my code. Maybe I
> >> >did something wrong??
> >> >Here is the view:
> >> >CREATE OR REPLACE VIEW CUSTOMER_CONFIG_VW
> >> >(CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED, DATE_MODIFIED,
> >> >SOURCE) AS
> >> >SELECT CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED,
> >> >DATE_MODIFIED", NULL source
> >> >FROM customer_config;
>
> >> I'm going to assume from here on out that Table1 is "CUSTOMER_CONFIG"
> >> and Table2 is "CUSTOMER_CONFIG_HIST". Replace with actual tablenames as
> >> needed.
>
> >> I am not familiar with this syntax, so I can't say if it's correct or
> >> not. But here's how I would write it:
>
> >> create or replace view customer_config_vw as
> >> select CUSTOMER_ID,
> >> PRODUCT_ID,
> >> FORMAT,
> >> STATUS,
> >> DATE_ADDED,
> >> DATE_MODIFIED,
> >> null SOURCE
> >> from CUSTOMER_CONFIG
> >> ;
>
> >> >Here is part of, not the entire, trigger:
> >> >CREATE OR REPLACE TRIGGER customer_config_trg
> >> > INSTEAD OF UPDATE OR INSERT ON customer_config_vw
> >> > FOR EACH ROW
> >> > INSERT INTO customer_config_hist
> >> > VALUES
> >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status,
> >> >:NEW.source,
> >> >SYSDATE);
>
> >> Ah, an UPDATE OR INSERT one. I can't check for syntax on this one since
> >> I'm at home, but here:
>
> >> create or replace trigger customer_config_trg
> >> INSTEAD OF UPDATE OR INSERT
> >> ON customer_config_vw
> >> FOR EACH ROW
> >> <var declarations if needed>
> >> BEGIN
> >> if INSERTING then
> >> -- insert into the main table
> >> INSERT INTO CUSTOMER_CONFIG
> >> (CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED,
> >> DATE_MODIFIED)
> >> VALUES (:new.CUSTOMER_ID, :new.PRODUCT_ID, :new.FORMAT, :new.STATUS,
> >> :new.DATE_ADDED, :new.DATE_MODIFIED);
>
> >> -- insert into the history table
> >> INSERT INTO CUSTOMER_CONFIG_HIST
> >> (CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED,
> >> DATE_MODIFIED, SOURCE)
> >> VALUES (:new.CUSTOMER_ID, :new.PRODUCT_ID, :new.FORMAT, :new.STATUS,
> >> :new.DATE_ADDED, :new.DATE_MODIFIED, :new.SOURCE);
>
> >> else
> >> -- update the main table
> >> UPDATE CUSTOMER_CONFIG
> >> [... fields ...]
> >> where [...];
>
> >> -- update the history table
> >> UPDATE CUSTOMER_CONFIG_HIST
> >> [...]
> >> where [... fields ...];
> >> end if;
> >> END;
> >> /
>
> >> >When I do the insert into the view I get:
> >> >ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()
> >> >+30] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
>
> >> That seems like a pretty generic error. I'm not sure I can help you much
> >> more beyond this, you'll probably just have to play around with the
> >> syntax. I have gotten this approach to work many many times in the past,
> >> though, so you should eventually be able to get it to work.
>
> >> --
> >> "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
>
> > Well, syntax looks correct. I may have to open an SR. But, I bet it
> > is something so simple I'm just missing it.
>
> 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.
>
> Shakespeare

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

     INSERT INTO customer_config_hist
     VALUES

(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, SYSDATE);
  END IF;   IF UPDATING THEN
     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;
/ Received on Wed Aug 06 2008 - 07:51:32 CDT

Original text of this message