Re: Can this be done with a trigger?

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 6 Aug 2008 10:23:18 +0200
Message-ID: <48995f76$0$49822$e4fe514c@news.xs4all.nl>

<artmerar_at_yahoo.com> schreef in bericht news: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 Received on Wed Aug 06 2008 - 03:23:18 CDT