Re: One more trigger question
Date: Tue, 8 Sep 2009 13:28:39 -0700 (PDT)
Message-ID: <6ae5578a-2b4d-4182-ba79-d14a5eef5acd_at_r39g2000yqm.googlegroups.com>
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 Received on Tue Sep 08 2009 - 15:28:39 CDT