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

On Sep 8, 2:51*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
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

On Sep 8, 3:28*pm, ddf <orat... (AT) msn (DOT) com> wrote:

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.

On 08.09.2009 21:51, The Magnet wrote:
There is no way you can get this done via a trigger on table ACTIVE
which updates INACTIVE because data is missing - as you have identified
yourself. Date which is not there cannot be inserted in tables.

Here are some other ideas:

Write a trigger for INACTIVE which raises an error if the combination
(customer_id, product_id) is present in ACTIVE effectively enforcing the
update sequence 1. delete from ACTIVE, 2. insert in INACTIVE which might
get you more safety.

Or write a trigger for ACTIVE which ensures the INACTIVE entry is
present on a DELETE and if not throws.

Or write an AFTER DELETE trigger for INACTIVE which deletes from ACTIVE
on insert. You could combine this with an before DELETE trigger on
ACTIVE which raises an error if the entry is not yet present in
INACTIVE. This ensures that you cannot manually delete from ACTIVE if
there is no matching entry present in INACTIVE.

What I'd probably also consider: add all entries from active with time
and source as NULL to INACTIVE. Drop table ACTIVE (or replace it with a
view; attention, this can be inefficient because you cannot index NULLS,
but there are ways around this). With this you get a nice PK
enforcement: combination (product_id, customer_id) is then really unique.

Since you hinted at the size of all this, you might even be able to
mimic the current situation of the separated tables by partitioning by
time and source thus enabling a fast scan for all active customers or
all active customers for a specific product.

Kind regards

robert


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

On Sep 8, 4:38*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
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

On 09.09.2009 06:57, Ed Prochak wrote:
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/