Re: Can this be done with a trigger?
- From: artmerar@xxxxxxxxx
- Date: Tue, 5 Aug 2008 09:48:26 -0700 (PDT)
On Aug 5, 11:43 am, gazzag <gar...@xxxxxxxxx> wrote:
On 5 Aug, 17:11, artme...@xxxxxxxxx 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.....
.
- Follow-Ups:
- Re: Can this be done with a trigger?
- From: Malcolm Dew-Jones
- Re: Can this be done with a trigger?
- From: Aya the Vampire Slayer
- Re: Can this be done with a trigger?
- From: Dan Blum
- Re: Can this be done with a trigger?
- References:
- Can this be done with a trigger?
- From: artmerar
- Re: Can this be done with a trigger?
- From: gazzag
- Can this be done with a trigger?
- Prev by Date: Re: Can this be done with a trigger?
- Next by Date: Re: Select one field/value from two different tables
- Previous by thread: Re: Can this be done with a trigger?
- Next by thread: Re: Can this be done with a trigger?
- Index(es):
Relevant Pages
|
