Обсуждение: question on writing a function

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.

For simplicity, the member table is just a few columns (id, active_status,
name). How would I grab the ID from that table the moment the trigger
fires to be able to pass it to my function?

I hope that¹s clear.

Thanking you in advance for any help.

Aaron

The contents of this message and any attachments to it are confidential and may be legally privileged.
If you have received this message in error you should delete it from your system immediately and advise the sender.
dunnhumby may monitor and record all emails. The views expressed in this email are those of the sender and not those of
dunnhumby.

On 01/22/2014 08:26 AM, Aaron Burnett 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.
>
> For simplicity, the member table is just a few columns (id, active_status,
> name). How would I grab the ID from that table the moment the trigger
> fires to be able to pass it to my function?
> I hope that¹s clear.

So how about an ON UPDATE TRIGGER on the member table that monitors the
active_status. When it changes grab the id for that row and go from
there. You did not say what pl language you are using so the details
will depend on that.



>
> Thanking you in advance for any help.
>
> Aaron
>
> The contents of this message and any attachments to it are confidential and may be legally privileged.
> If you have received this message in error you should delete it from your system immediately and advise the sender.
> dunnhumby may monitor and record all emails. The views expressed in this email are those of the sender and not those
ofdunnhumby. 
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 01/22/2014 08:26 AM, Aaron Burnett 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.
>
> For simplicity, the member table is just a few columns (id, active_status,
> name). How would I grab the ID from that table the moment the trigger
> fires to be able to pass it to my function?
>
> I hope that¹s clear.
>
> Thanking you in advance for any help.

Something more concrete assuming using plpgsql Also just a skeleton
function to demonstrate fetching id.

CREATE OR REPLACE FUNCTION status_update()
   RETURNS trigger AS
DECLARE
     m_id int;
$Body$
BEGIN
     IF NEW.active_status = 'f' THEN
         m_id := NEW.id;
         <more code>
     END IF;
RETURN NEW;
END;
$Body$
   LANGUAGE plpgsql;
>
> Aaron



--
Adrian Klaver
adrian.klaver@gmail.com

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