October 15, 2007 at 10:29 pm
Yep... as I said, no need for the trigger.
Also, if I get it correctly, there may also be no need for the EmailOut table. The EmailOut Table can come as a temporary one, just to hold the info you need for the emailing.
From the job you created, add a step that gets the record for emailing, process the records and email the concerned in the loop.
**Note, your job already know which records are for emailing. These are the ones you update the flag with.
October 16, 2007 at 9:16 am
Ronald San Juan (10/15/2007)
Yep... as I said, no need for the trigger.Also, if I get it correctly, there may also be no need for the EmailOut table. The EmailOut Table can come as a temporary one, just to hold the info you need for the emailing.
From the job you created, add a step that gets the record for emailing, process the records and email the concerned in the loop.
**Note, your job already know which records are for emailing. These are the ones you update the flag with.
You were *exactly* dead on Ronald. I simply changed my nightly update job to select the records into a temp table first, then run the email procedure,and then finally to make the update that was there originally. So there was no need for a trigger after all. Thanks everyone for helping me to understand what I needed.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 16, 2007 at 7:53 pm
Ronald San Juan (10/15/2007)
Yep... as I said, no need for the trigger.
As well as "Flag" columns, and most of those 25 columns, and that overnight job selecting records for emailing.
"Send Email" procedure can do it all.
Also, if I get it correctly, there may also be no need for the EmailOut table. The EmailOut Table can come as a temporary one, just to hold the info you need for the emailing.
From the job you created, add a step that gets the record for emailing, process the records and email the concerned in the loop.
**Note, your job already know which records are for emailing. These are the ones you update the flag with.
It's a good practice to test your approach against database holding 3..5 years amount of data.
Populate this table with million rows and you'll see - it sucks.
I would stick with trigger and EmailOut table.
But no job.
Every time record is created trigger copies required data to EmailOut and set "delivery date" to 5 days from now.
Every time record has got updated with proper customer response trigger removes corresponding row from EmailOut.
"Send Email" procedure picks up all records from EmailOut having "Delivery Date" after its last run.
It should update all "5 days notice" records with new "delivery date" - 5 days later then it's been recorded and make it "10 days notice", all "10 days notice" will become "NN days notice", etc.
Until trigger has removed those records upon requested response from customer.
This table is not gonna grow, even if one day there happened to be too many bad customers operations on that table not gonna interfere with normal activity by locking common resources.
Of course I would normalise EmailOut putting varchar names in look-up tables.
Not to mention clustered index on "delivery date" field.
_____________
Code for TallyGenerator
October 16, 2007 at 8:44 pm
[/quote]
Every time record is created trigger copies required data to EmailOut and set "delivery date" to 5 days from now.
Every time record has got updated with proper customer response trigger removes corresponding row from EmailOut.
[/quote]
Sergiy, wouldn't I then need two triggers? An insert trigger to insert the information into the email table, then an update trigger to delete the record from the email table if the date field in question has been populated prior to five days, or any number of days I specify? I guess I was glad to find a trigger-less solution because I did not know what will be the cost of the update trigger firing every time any of the fields are updated. I like your approach, though, its almost as if I would be loading all the records to be ready for emailing when the time comes rather than loading them only when they need to be sent, which is what I am doing now. Also, I do not know how I would tailor a permanent email table to fit the needs of several other mail procedures I have. I will have to test out your strategy. It might be a few days, but I am sure I will post back with some questions. Thank you for your input.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 16, 2007 at 9:45 pm
First of all - trigger is a stored procedure. It's just invoked in a different way.
Look in "CREATE TRIGGER" topic in BOL if you don't believe me.
🙂
"Trigger-less solution" means just replacing one SP with another one, probably less effective and more resource consuming. It's not always the case, as everywhere, "it depends".
So, point is not in getting rid of triggers. Point is in finding most effective solution.
Second, there is UPDATE() function in triggers for your service.
You FOR UPDATE trigger should act only if "response" column is updated.
It will skip whole trigger if any other column(s) is updated but not "response".
No overhead here.
And third, your EmailOut table should have a column specifying which emailing process every particular row is inserted for. Then every process can recognise its records and process only them. Saying that I would rethink my last statement and suggest clustered index on (ProcessID, DeliveryDate), not just (DeliveryDate)
_____________
Code for TallyGenerator
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply