Write to table, send email, mark as sent

Question

Write to table, send email, mark as sent

jcobb 20350

Default port

Points: 1438
More actions
February 8, 2019 at 7:49 am

#370536

I have a few triggers but know they can cause issues if everything doesn't fire correctly. What I would like is to do is write the data to a table or use a view to collect the data. I'd then create a job to scan the table, look for any that had not been emailed, and email those rows. I can do all of this no problem.
Question is, how do I mark the rows as having been emailed once I scan the table/view? I'm thinking I could have a field named emailed which would be NULL (or I could script to N). The job would look at the table to find Nulls or N and email that row. At the same time, it would change the field to Y so the next time the job ran, that row would be excluded.
I'm up to learning new tricks so I'm open to suggestions.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

I don't know enough of your process to say for sure but it sounds like you have a plan that will work just fine. Guess I'd avoid NULL and use "N" here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcobb 20350 Default port Points: 1438 More actions · Answer 2

I have the table already and the "emailed" column is NULL. I want to create a SQL job that looks at that table and emails any rows where the email is NULL. Once emailed, I need to set that column to "Y". The next time the job runs, I want to ignore any that have the Y flag because they have already been sent.

I just don't know how to set the flag to Y once the SQL job sends out emails. That's what I need help doing.

pietlinden SSC Guru Points: 63421 More actions · Answer 3

Since sending e-mails is done serially, you could just use a cursor for this. (yeah yeah, I know... cursors are terrible and all that, but I think this is a good place for one.)
Open a cursor with just the info you need, send the e-mail, then update the cursor. (I understand that means you would need to use an updateable cursor.