Re: One more trigger question
Date: Tue, 08 Sep 2009 22:39:15 +0200
Message-ID: <7gntoaF2qhe9iU1_at_mid.individual.net>
On 08.09.2009 21:51, The Magnet 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.....
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/Received on Tue Sep 08 2009 - 15:39:15 CDT