Re: One more trigger question

On 09.09.2009 06:57, Ed Prochak wrote:
On Sep 8, 4:38 pm, The Magnet <a...@xxxxxxxx> wrote:
On Sep 8, 3:28 pm, ddf <orat...@xxxxxxx> wrote:



On Sep 8, 2:51 pm, The Magnet <a...@xxxxxxxx> 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/
.

Relevant Pages

  • Re: One more trigger question
    ... They also have a history table which shows all the opt outs and opt ...  So I figured a trigger on the ... SQL> create table inactive( ...
    (comp.databases.oracle.misc)
  • Re: One more trigger question
    ...  So I figured a trigger on the ... SQL> create table prod_to_source( ...   2  before insert or update on active ... a customer opts out of some email product. ...
    (comp.databases.oracle.misc)
  • Re: One more trigger question
    ...  So I figured a trigger on the ... SQL> create table inactive( ...   2  before insert or update on active ... a customer opts out of some email product. ...
    (comp.databases.oracle.misc)
  • Re: Limit WSS Version History, e.g. to 3 Versions
    ... If you want to limit your version history to 3 for ALL your lists, ... can also use a trigger in SQL to manage your versions. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)