 | |
#1
 
| |||
| |||
|
Can this be done with a trigger? -
08-05-2008
, 11:11 AM
#2
| |||
| |||
|
|
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? |
#3
| |||
| |||
|
|
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? |
#4
| |||
| |||
|
|
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? |
#5
| |||
| |||
|
|
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? |
#6
| |||
| |||
|
|
On 5 Aug, 17:11, artme... (AT) yahoo (DOT) 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? |
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.....
#7
| |||
| |||
|
|
On 5 Aug, 17:11, artme... (AT) yahoo (DOT) 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? |
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.....
#8
| |||
| |||
|
|
On 5 Aug, 17:11, artme... (AT) yahoo (DOT) 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? |
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.....
#9
| |||
| |||
|
|
On 5 Aug, 17:11, artme... (AT) yahoo (DOT) 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? |
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.....
#10
| |||
| |||
|
|
On Aug 5, 11:43 am, gazzag <gar... (AT) jamms (DOT) org> wrote: On 5 Aug, 17:11, artme... (AT) yahoo (DOT) 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..... |
(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 (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.