Can this be done with a trigger?
in [Oracle Help]
|
From: artmerar on 5 Aug 2008 12:11 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?
From: gazzag on 5 Aug 2008 12:43 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?
From: artmerar on 5 Aug 2008 12:48 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? All I can think of is to update the record from the PL/SQL procedure once the trigger gets done........ So, say I have a procedure like this: PROCEDURE ADD ( p_customer_id NUMBER, p_product_id VARCHAR2, p_source VARCHAR2 DEFAULT 'U') IS INSERT INTO customer_config VALUES (p_customer_id, p_product_id, 'Text', 'Active', SYSDATE); 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.....
From: Dan Blum on 5 Aug 2008 13:09 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? All I can think of is to update the record from the PL/SQL > procedure once the trigger gets done........ > So, say I have a procedure like this: > PROCEDURE ADD ( > p_customer_id NUMBER, > p_product_id VARCHAR2, > p_source VARCHAR2 DEFAULT 'U') IS > INSERT INTO customer_config > VALUES (p_customer_id, p_product_id, 'Text', 'Active', SYSDATE); > 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..... And if someone inserts a row in the table without using the PL/SQL program (the scenario you were worried about above), how does SOURCE get populated? The trigger has to be able to assume a value for SOURCE, or this will not work. -- _______________________________________________________________________ Dan Blum tool(a)panix.com "I wouldn't have believed it myself if I hadn't just made it up."
From: artmerar on 5 Aug 2008 13:16
On Aug 5, 12:09 pm, t...(a)panix.com (Dan Blum) wrote: > artme...(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? All I can think of is to update the record from the PL/SQL > > procedure once the trigger gets done........ > > So, say I have a procedure like this: > > PROCEDURE ADD ( > > p_customer_id NUMBER, > > p_product_id VARCHAR2, > > p_source VARCHAR2 DEFAULT 'U') IS > > INSERT INTO customer_config > > VALUES (p_customer_id, p_product_id, 'Text', 'Active', SYSDATE); > > 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..... > > And if someone inserts a row in the table without using the PL/SQL program > (the scenario you were worried about above), how does SOURCE get populated? > The trigger has to be able to assume a value for SOURCE, or this will not > work. > > -- > _______________________________________________________________________ > Dan Blum t...(a)panix.com > "I wouldn't have believed it myself if I hadn't just made it up." SOURCE will have a default value if not specified. I guess my main question is, can I somehow populate source for the trigger to handle when SOURCE is not in the triggering table....?? |