Thread: 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
On Wed, Jan 22, 2014 at 12:08 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
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.
Agreed - Your active_status could have three statuses - Active, Inactive, and Deleted. The front end switches it to Inactive, and the cron job looks for all Inactive users, deletes their data and sets them to Deleted.
Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
"Quality is not an act, it is a habit." -- Aristotle
"Quality is not an act, it is a habit." -- Aristotle