Re: Can this be done with a trigger?

From: <artmerar_at_yahoo.com>
Date: Tue, 5 Aug 2008 13:42:57 -0700 (PDT)
Message-ID: <b2909dcd-781e-41b4-8c9c-01f29c6f03ad@y21g2000hsf.googlegroups.com>

On Aug 5, 3:26 pm, Aya the Vampire Slayer <ry..._at_gatech.rmv.this.part.edu> wrote:
> artme..._at_yahoo.com wa:
>
>
>
> >On Aug 5, 3:07 pm, Aya the Vampire Slayer
> ><ry..._at_gatech.rmv.this.part.edu> wrote:
> >> artme..._at_yahoo.com wa:
> >> >On Aug 5, 12:16 pm, Aya the Vampire Slayer
> >> ><ry..._at_gatech.rmv.this.part.edu> wrote:
> >> >> artme..._at_yahoo.com wa:
> >> >> <snip>
> >> >> >The SOURCE is not needed in this table. But, this would fire the
> >> >> >trigger where SOURCE is needed. The SOURCE will be derived within the
> >> >> >PL/SQL code. I do not think this is possible. I may have to let the
> >> >> >trigger fire, then go back and update the row.....
>
> >> >> Create a View on Table1 selecting * from Table1 and also adding a null
> >> >> column called SOURCE at the end.
>
> >> >> create or replace view vw_tbl1 as
> >> >> select t.*,
> >> >> null SOURCE
> >> >> from table1 t
> >> >> ;
>
> >> >> Change your PL/SQL procedure to insert into the view instead of directly
> >> >> into the table, passing whatever it is that the PL/SQL is able to figure
> >> >> out for the SOURCE column as part of the INSERT parameters (since you
> >> >> are now inserting into the view where SOURCE exists as a column).
>
> >> >> Then, create your trigger on the view and have the trigger insert all of
> >> >> the information into Table2, including the SOURCE column, after
> >> >> inserting into table1.
>
> >> >> If you are worried about people inserting directly into the table, then
> >> >> simply deny access to the table and only allow access to the view. (here
> >> >> is me hoping you aren't letting users log in directly to the schema that
> >> >> contains all of these objects...)
> >> >I must say, I do like your approach, that is, if if it did not yield
> >> >this result:
> >> >Tue Aug 5 14:41:58 2008
> >> >Errors in file /u01/NI00/admin/udump/ni00_ora_13221.trc:
> >> >ORA-07445: exception encountered: core dump [_intel_fast_memcpy.J()
> >> >+30] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
> >> >More investigation is needed. Hope I do not have to log an SR with
> >> >Oracle....
>
> >> I'm not sure if it would make a difference for what your error is, but I
> >> guess I should also note that I almost always use INSTEAD OF triggers
> >> with views (and not AFTER triggers). For INSTEAD OF, you'll obviously
> >> have to write both table inserts (for table1 and table2) into the
> >> trigger explicitly.
> >Ok, Well, maybe I'm a dingbat here, but help me out:
> >I create a view: CREATE VIEW V1 as SELECT TABLE1.*, NULL SOURCE FROM
> >TABLE1;
> >That creates a view identical to TABLE1 with an extra column named
> >SOURCE.
> >Now, in my INSTEAD OF trigger I can insert into any table I code for,
> >right? So, within the body of the trigger I can say: INSERT INTO
> >TABLEB VALUES (.........).
> >That should work, right? I'm just confused on where the originating
> >DML operation goes. Is the purpose of the trigger to not insert into
> >the base tables, but just execute the code?
>
> For an INSTEAD OF trigger, you will have to write both the "INSERT INTO
> TABLE1 [...]" and "INSERT INTO TABLE2 [...]" DML statements into the
> body of the trigger.
>
> "INSTEAD OF", as the name implies, does whatever is in the body of the
> trigger instead of doing the actual DML action that triggered it. So,
> for an INSTEAD OF INSERT trigger, if you want it to actually do the
> INSERT that triggered it in the first place, you will have to write it
> into the body of the trigger explicitly. (for the record, INSTEAD OF
> triggers can only be used with views)
>
> --
> "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

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;

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); 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] [] []

Any thoughts? Received on Tue Aug 05 2008 - 15:42:57 CDT