Re: Can this be done with a trigger?

On Aug 5, 12:09 pm, t..._at_panix.com (Dan Blum) wrote:
> artme..._at_yahoo.com wrote:
> > On Aug 5, 11:43 am, gazzag <gar..._at_jamms.org> wrote:
> > > On 5 Aug, 17:11, artme..._at_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..._at_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....?? Received on Tue Aug 05 2008 - 12:16:09 CDT