Re: One more trigger question

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 09 Sep 2009 07:53:07 +0200
Message-ID: <7gou71F2pco02U1_at_mid.individual.net>

On 09.09.2009 06:57, Ed Prochak wrote:

> 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??

As far as I understand it's input. The source is the channel which the user used to deregister (e.g. phone, email, a certain website).

Cheers

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Received on Wed Sep 09 2009 - 00:53:07 CDT