Can this be done with a trigger?
in [Oracle Help]
|
From: artmerar on 5 Aug 2008 16:17 On Aug 5, 3:07 pm, Aya the Vampire Slayer <ry...(a)gatech.rmv.this.part.edu> wrote: > artme...(a)yahoo.com wa: > > > > >On Aug 5, 12:16 pm, Aya the Vampire Slayer > ><ry...(a)gatech.rmv.this.part.edu> wrote: > >> artme...(a)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. > > -- > "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 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?
From: Aya the Vampire Slayer on 5 Aug 2008 16:26 artmerar(a)yahoo.com wa: >On Aug 5, 3:07 pm, Aya the Vampire Slayer ><ry...(a)gatech.rmv.this.part.edu> wrote: >> artme...(a)yahoo.com wa: >> >On Aug 5, 12:16 pm, Aya the Vampire Slayer >> ><ry...(a)gatech.rmv.this.part.edu> wrote: >> >> artme...(a)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
From: artmerar on 5 Aug 2008 16:42 On Aug 5, 3:26 pm, Aya the Vampire Slayer <ry...(a)gatech.rmv.this.part.edu> wrote: > artme...(a)yahoo.com wa: > > > > >On Aug 5, 3:07 pm, Aya the Vampire Slayer > ><ry...(a)gatech.rmv.this.part.edu> wrote: > >> artme...(a)yahoo.com wa: > >> >On Aug 5, 12:16 pm, Aya the Vampire Slayer > >> ><ry...(a)gatech.rmv.this.part.edu> wrote: > >> >> artme...(a)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?
From: Aya the Vampire Slayer on 5 Aug 2008 17:11 artmerar(a)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
From: artmerar on 5 Aug 2008 17:20
On Aug 5, 4:11 pm, Aya the Vampire Slayer <ry...(a)gatech.rmv.this.part.edu> wrote: > artme...(a)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. |