Re: Can this be done with a trigger?

artmerar_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

>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