Re: One more trigger question

From: Ed Prochak <edprochak_at_gmail.com>
Date: Tue, 8 Sep 2009 21:57:17 -0700 (PDT)
Message-ID: <443b9ded-09e3-44f8-8153-df59ae391f8e_at_e12g2000yqi.googlegroups.com>

On Sep 8, 4:38 pm, The Magnet <a..._at_unsu.com> wrote:
> On Sep 8, 3:28 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
> > On Sep 8, 2:51 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > Ok, this one represents a dumb setup by the client.  The client has a
> > > table which holds which email product(s) his customers get.  Call this
> > > table 'ACTIVE':
>
> > > ACTIVE
> > > ------------
> > > CUSTOMER_ID
> > > PRODUCT_ID
>
> > > They also have a history table which shows all the opt outs and opt
> > > ins.  Call this table INACTIVE:
>
> > > INACTIVE
> > > ---------------
> > > CUSTOMER_ID
> > > PRODUCT_ID
> > > TIME
> > > SOURCE
>
> > > The SOURCE column identifies where the opt out came from.  AOL,
> > > HOTMAIL, In House, etc.  Problem is that the active & history tables
> > > are not in sync and do not properly reflect the activities.  The
> > > system is way to big to put in fixes.  So I figured a trigger on the
> > > ACTIVE table and start recording to a new history table.  The active
> > > table is correct, just not the history table.  A trigger will make
> > > sure every change to the active table is recorded.
>
> > > Problem being that the SOURCE column does not come into account when
> > > dealing with the ACTIVE table.  So, I do not think a trigger works on
> > > the ACTIVE table as you cannot just pass values using a trigger.
>
> > > So problem being, how to put a trigger on the active table such that
> > > when a DELETE occurs we can properly record the SOURCE in the history
> > > table.  The trigger will ensure the history is recorded properly.
>
> > > Dumb, very dumb.  Not even sure INSTEAD OF triggers will work
> > > here.....
>
> > INSTEAD OF triggers are used for  complex or multi-table views (those
> > views not where the data is not directly modifiable).  Is PRODUCT_ID
> > in any way related to SOURCE?  If not, can it be related through a
> > 'lookup' table?  If the answer to that last question is 'Yes' then you
> > have a way to get your SOURCE values:
>
> > SQL> create table active(
> >   2          customer_id varchar2(40),
> >   3          product_id varchar2(40)
> >   4  );
>
> > Table created.
>
> > SQL>
> > SQL> create table inactive(
> >   2          customer_id varchar2(40),
> >   3          product_id varchar2(40),
> >   4          time date,
> >   5          source varchar2(40)
> >   6  );
>
> > Table created.
>
> > SQL>
> > SQL> create table prod_to_source(
> >   2          product_id varchar2(40),
> >   3          source varchar2(40)
> >   4  );
>
> > Table created.
>
> > SQL>
> > SQL> insert all
> >   2  into prod_to_source
> >   3  values('Microsoft','hotmail')
> >   4  into prod_to_source
> >   5  values('AOL','aolmail')
> >   6  into prod_to_source
> >   7  values('Google','gmail')
> >   8  into prod_to_source
> >   9  values('Yahoo','yahoo mail')
> >  10  select * From dual;
>
> > 4 rows created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> create or replace trigger active_inactive_trg
> >   2  before insert or update on active
> >   3  for each row
> >   4  declare
> >   5          src varchar2(40);
> >   6  begin
> >   7          select source
> >   8          into src
> >   9          from prod_to_source
> >  10          where product_id = :new.product_id;
> >  11
> >  12          insert into inactive
> >  13          (customer_id, product_id, time, source)
> >  14          values
> >  15          (:new.customer_id, :new.product_id, sysdate, src);
> >  16
> >  17  end;
> >  18  /
>
> > Trigger created.
>
> > SQL>
> > SQL> insert all
> >   2  into active
> >   3  (customer_id, product_id)
> >   4  values
> >   5  ('Larchmont Fescue','Google')
> >   6  into active
> >   7  (customer_id, product_id)
> >   8  values
> >   9  ('Wachovia Snarfblatt','AOL')
> >  10  into active
> >  11  (customer_id, product_id)
> >  12  values
> >  13  ('Parchment Wonkavision','Microsoft')
> >  14  select * from dual;
>
> > 3 rows created.
>
> > SQL>
> > SQL> set linesize 160
> > SQL>
> > SQL> select * From active;
>
> > CUSTOMER_ID                              PRODUCT_ID
> > ----------------------------------------
> > ----------------------------------------
> > Larchmont Fescue                         Google
> > Wachovia Snarfblatt                      AOL
> > Parchment Wonkavision                    Microsoft
>
> > SQL>
> > SQL> select * from inactive;
>
> > CUSTOMER_ID
> > PRODUCT_ID                               TIME                 SOURCE
> > ----------------------------------------
> > ---------------------------------------- --------------------
> > ------------------------
> > Larchmont Fescue
> > Google                                   08-SEP-2009 15:27:28 gmail
> > Wachovia Snarfblatt
> > AOL                                      08-SEP-2009 15:27:28 aolmail
> > Parchment Wonkavision
> > Microsoft                                08-SEP-2009 15:27:28 hotmail
>
> > SQL>
>
> > David Fitzjarrell
>
> David,
>
> Your example is good, however this is only for DELETES.  But the
> PRODUCT_ID has nothing to do with the SOURCE.  The SOURCE is simply
> where the opt out request came from.  So, the INACTIVE table has an
> extra column I want to populate at the time of DELETING from the
> ACTIVE table.
>
> So, a customer opts out of some email product.  The request came from
> his AOL account, or whatever.  The record gets deleted from the ACTIVE
> table and a record gets inserted into the INACTIVE table displaying
> the customer ID, product ID, time and SOURCE value.
>
> Now, there are only 9 defined SOURCE values, but they do not have
> anything to do with the actual PRODUCT ID.
>
> I do not think this is really possible.  The customer really messed
> this design up.

So how does the (admittedly broken) application code know the source? Is it in some other table that can be queried in the trigger??

  Ed Received on Tue Sep 08 2009 - 23:57:17 CDT