Can this be done with a trigger?
in [Oracle Help]
|
From: Aya the Vampire Slayer on 5 Aug 2008 13:16 artmerar(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...) -- "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 13:50 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...) > > -- > "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 Good suggestion. Thanks.
From: Malcolm Dew-Jones on 5 Aug 2008 15:15 artmerar(a)yahoo.com wrote: : On Aug 5, 11:43 am, gazzag <gar...(a)jamms.org> wrote: : > On 5 Aug, 17:11, artme...(a)yahoo.com wrote: : > : > : > : > > Hi, : > : > > We have a table like this: : > : > > CUSTOMER_ID : > > PRODUCT_ID : > > FORMAT : > > STATUS : > > DATE_ADDED : > > DATE_MODIFIED : > : > > We also have a history table like this: : > : > > CUSTOMER_ID : > > PRODUCT_ID : > > FORMAT : > > STATUS : > > SOURCE : > > DATE_ADDED : > : > > We want to put a trigger on the first table such that when a record is : > > inserted or updated it will make the entry to the history table. : > > However, the problem is the 'SOURCE' column on the history table. It : > > is not present or needed in the first table. : > : > > Entries into the first table are done through a PL/SQL program. So, : > > what we want is that when the entry is made into the first table, it : > > fires the trigger to make the second entry. But, how can this be done : > > if the columns do not match? : > : > > We do not want to code it in the PL/SQL program because then someone : > > can make an entry to the first table and we cannot record the history : > > on it, yet the SOURCE column only gets populated in the PL/SQL : > > program.... : > : > > And, to make things more complicated, depending on what is being : > > UPDATED, we may need the values of :NEW or :OLD........ : > : > > Is there a way to do this? : > : > Sure there is. What is SOURCE though? : Gazzag, : I am trying to write the trigger. Problem is since you cannot pass : parameters to triggers, where does the trigger get the value for : SOURCE? What is supposed to go into SOURCE, and how do you know what the value is? What ever it is, you have to set it up before hand. Anyway, if for example you want the name of the pl/sql procedure that contains the sql statements, then perhaps something like this (very bad code, just quick examples...) procedure foobag (eg1 number, eg2 number ) is begin my_utils.which_proc('foobag'); insert into blazzer (one,two) values (eg1, eg2); end; create package [body] my_utils -- need a header and a body which_proc varchar2(70); procedure which_proc( p ) is begin which_proc := p; end; end my_utils; create trigger blazzer_trigger on blazzer begin insert into blazzer_log (one,two,source) values (:new.one,:new.two, my_utils.which_proc); end;
From: artmerar on 5 Aug 2008 15:48 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...) > > -- > "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 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....
From: Aya the Vampire Slayer on 5 Aug 2008 16:07
artmerar(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 |