On Wed, Jan 22, 2014 at 10:26 AM, Aaron Burnett
<Aaron.Burnett@us.dunnhumby.com> wrote:
>
> Greetings all,
>
> I *think* there¹s a way to accomplish what I want, but it is eluding me at
> this time. Any help or pointers will be greatly appreciated.
>
> What I am trying to accomplish is that when a member deactivates their
> account through the UI, my trigger will call a function that deletes or
> archives their data. The issue being that we don¹t want the UI to have to
> wait for this process because of the huge amount of data and tables that
> the queries will have to go through, so we are avoiding doing it
> programatically through the UI and I want it to happen in the background.
>
> The problem is this (and I may just not be thinking clearly): Everything
> is tied back to the member¹s ID which is the constant throughout the
> tables that I will need to manipulate. My trigger will call the (to be
> written) function that will do all the heavy lifting when the
> active_status changes from Œa¹ to Œx¹, but I somehow need to get the ID of
> that member and pass it on to the function so it can then do all that it
> needs to.
All of this is assuming you are not using RI which is the easiest way
to do cascading deletes for sure but occasionally has to be avoided
due to performance constraints.
Generally it is not a good idea to do heavy lifting in triggers.
What I would advise in this case is to organize deleted user IDs in a
deletion queue. You do that by making a 'users to be deleted table'
where you store the users pending deletion (which is the one and only
thing that happens at trigger time). Then you set up batch process
(say, via cron) that cleans out the data at a convenient time.
Or, you bypass the trigger completely and the have the application
simply write directly to the 'to be deleted table', perhaps setting a
soft deletion flag on the table. That way, you maybe can utilize RI.
merlin
merlin