Re: One more trigger question

From: The Magnet <art_at_unsu.com>
Date: Tue, 8 Sep 2009 13:38:43 -0700 (PDT)
Message-ID: <236c3ea1-d9e8-4f90-b84b-f2cabaa39dd6_at_z24g2000yqb.googlegroups.com>

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. Received on Tue Sep 08 2009 - 15:38:43 CDT