Can this be done with a trigger?
in [Oracle Help]
|
From: DA Morgan on 5 Aug 2008 19:28 artmerar(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? In what version of Oracle? Assuming 9i or above you are reinventing the wheel: Use Fine Grained Auditing. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan(a)x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
From: artmerar on 5 Aug 2008 20:24 On Aug 5, 6:28 pm, DA Morgan <damor...(a)psoug.org> wrote: > 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? > > In what version of Oracle? > > Assuming 9i or above you are reinventing the wheel: Use Fine Grained > Auditing. > -- > Daniel A. Morgan > Oracle Ace Director & Instructor > University of Washington > damor...(a)x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - > > - Show quoted text - We are running Oracle 10g R2. 10.2.0.1.0 to be exact.......does this tell you anyhting??
From: Shakespeare on 6 Aug 2008 04:23 <artmerar(a)yahoo.com> schreef in bericht news:747980e0-ff34-4330-b48d-f3735df58bd0(a)d77g2000hsb.googlegroups.com... > 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. > 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
From: artmerar on 6 Aug 2008 08:51 On Aug 6, 3:23 am, "Shakespeare" <what...(a)xs4all.nl> wrote: > <artme...(a)yahoo.com> schreef in berichtnews:747980e0-ff34-4330-b48d-f3735df58bd0(a)d77g2000hsb.googlegroups.com... > > > > > 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. > > 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 The trigger fires on insert or update. The actual trigger code is here. Do you see anything wrong with it? CREATE OR REPLACE TRIGGER customer_config_trg INSTEAD OF UPDATE OR INSERT ON customer_config_vw FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO customer_config_hist VALUES (:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, SYSDATE); END IF; IF UPDATING THEN IF :NEW.format IS NOT NULL THEN INSERT INTO customer_config_hist VALUES (:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, :NEW.source, SYSDATE); ELSIF :NEW.status IS NOT NULL THEN INSERT INTO customer_config_hist VALUES (:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, :NEW.source, SYSDATE); END IF; END IF; END; /
From: Aya the Vampire Slayer on 6 Aug 2008 09:09
artmerar(a)yahoo.com wa: >On Aug 6, 3:23 am, "Shakespeare" <what...(a)xs4all.nl> wrote: >> 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. >> >The trigger fires on insert or update. The actual trigger code is >here. Do you see anything wrong with it? >CREATE OR REPLACE TRIGGER customer_config_trg > INSTEAD OF UPDATE OR INSERT ON customer_config_vw > FOR EACH ROW >BEGIN > IF INSERTING THEN You also need to write your INSERT dml for the main table here before inserting into the HIST table. An INSTEAD OF trigger doesn't do it automatically like BEFORE/AFTER triggers do. INSERT INTO CUSTOMER_CONFIG VALUES (...); > INSERT INTO customer_config_hist > VALUES >(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, >SYSDATE); > END IF; > IF UPDATING THEN Same here, you also need to write the UPDATE dml for the main table here before inserting an entry into the HIST table. UPDATE CUSTOMER_CONFIG SET FOO = :new.FOO (...) where (...); > IF :NEW.format IS NOT NULL THEN > INSERT INTO customer_config_hist > VALUES >(:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, :NEW.source, >SYSDATE); > ELSIF :NEW.status IS NOT NULL THEN > INSERT INTO customer_config_hist > VALUES >(:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, :NEW.source, >SYSDATE); > END IF; > END IF; >END; >/ -- "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 |