Re: Can this be done with a trigger?

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. Received on Tue Aug 05 2008 - 16:20:40 CDT