October 15, 2007 at 1:52 pm
I apologize if this looks like crap, but I can not figure out how to save the tabs and spaces.
--This whole thing is about sending emails to people who have entered a record
--into a table, the info of whom is captured by GETDATE() and SYSTEM_USER.
--The records are engineering jobs that need to be sent back to our engineering
--group for revision. When the issue is resolved, the user populates the Resolve_dt
--field. The target is for the issue to be fixed within 5 days, but sometimes it is
--longer, thus the need for all this. The gist of it is that if 5 days have gone by and
--the resolve_dt is not populated, on the 6th day an email needs to be sent to the user
--reminding him of the issue that still needs to be resolved. Then at 10 and 20 days
--and email gets sent to the users boss with the user as CC.
tblTest
(
ID INT IDENTITY(1,1),
EWO VARCHAR(20) UNIQUE NOT NULL,
ENTERED_DT SMALLDATETIME DEFAULT GETDATE(),
ENTERED_BY VARCHAR(30) DEFAULT SYSTEM_USER,
RESOLVE_DT SMALLDATETIME,
FiveDayFlag BIT DEFAULT(0),
TenDayFlag BIT DEFAULT(0),
TwentyDayFlag BIT DEFAULT(0)
)
--I have a job that runs every night to set the bit fields of my tblTest.
--It calculates the number of working days(thanks to Jeff Moden) since
--the entered_dt field value, where the resolove_dt field is null. So, in theory
--only *one* of the bit fields will get set to 1 on any given night, starting
--with FiveDayFlag, then Ten and Twenty. No problems here.
CREATE TRIGGER tr_tblTest_mail_notification
ON tblTest
FOR UPDATE AS
BEGIN
--Load temp table with data from update to be used
--during the execute mail procedure step. I have a users table
--that has field 'LOGIN_NM' that is the value returned by SYSTEM_USER,
--so I can get a person's first and last name from my users table, rather than
--display the SYSTEM_USER name. No problems here
SELECT
ID = IDENTITY(INT,1,1),
i.EWO,
ENTERED_BY = u.First_name + ' ' + u.Last_Name,
MessageNumber = --This will be the number of days past entered_date
CASE WHEN d.FiveDayFlag = 0 AND i.FiveDayFlag = 1 THEN 5
WHEN d.TenDayFlag = 0 AND i.TenDayFlag = 1 THEN 10
WHEN d.TwentyDayFlag = 0 AND i.TwentyDayFlag = 1 THEN 20
ELSE NULL
END,
EmailAddress = u.email,--will be the email address mail is sent to
EmailSent = 0 --Will be changed to 1 when mail is sent for each record
INTO #List
FROM Inserted i INNER JOIN
Deleted d
ON i.ewo = d.ewo LEFT OUTER JOIN
tblUsers u
ON d.entered_by = u.login_nm
--No issues on any of the above in terms of performance
--I guess my general question is if there is a better way to compile
--a table of values to use for the email procedure outside of the trigger,
--rather than sending one email then looping back to the beginning?
--Local variables to be used in Email procedure
--send_cdosysmailattachment is a mail procedure I got from somewhere,
--with variables @To,@CC,@From,@Subject,@Message.
DECLARE @MaxID INT, --should be the count of records in #List
@MinID INT, --Lowet id in #List where mailsent = 0
@Entered_By VARCHAR(100),
@CCEmail VARCHAR(100),
@EWO VARCHAR(20),
@NumDays INT,
@SubjectLine VARCHAR(50),
@Message VARCHAR(500),
@Recipient VARCHAR(100)
SELECT @MinID = (SELECT MIN(id)
FROM #List
WHERE MessageNumber IS NOT NULL
AND EmailSent = 0)
SELECT @MaxID = (SELECT MAX(id)
FROM #List
WHERE MessageNumber IS NOT NULL)
WHILE @MinID <= @MaxID
BEGIN
SELECT @EWO = EWO,
@Entered_By = ENTERED_BY,
@NumDays = MessageNumber
FROM #list
WHERE id = @MinID
SELECT @SubjectLine = 'Return status ' + @EWO
SELECT @Message = @EWO + ' was returned to ENG by ' + @Entered_by + ' ' + CONVERT(VARCHAR(2),@NumDays) + ' '
+ 'days ago, with no resolution yet.'
--The @Recipient variable needs to change based on whether the job is 5,10, 02
--20 days old. If it is five the email need only go to the user. If it is 10 or 20
--it needs to go the users boss, copying the user. The boss's email will be hard coded.
SELECT @Recipient = CASE WHEN MessageNumber = 5 THEN EmailAddress
WHEN MessageNumber = 10 THEN 'boss@email'
WHEN MessageNumber = 20 THEN 'boss@email'
END
FROM #List
WHERE id = @MinID
SELECT @CCEmail = CASE WHEN MessageNumber = 5 THEN NULL
WHEN MessageNumber = 10 THEN EmailAddress
WHEN MessageNumber = 20 THEN EmailAddress
END
FROM #List
WHERE id = @MinID
EXECUTE send_cdosysmailattachment
@From = 'NO REPLY',
@To = @Recipient,
@cc = @CCEmail,
@Subject = @SubjectLine,
@Body = @Message
UPDATE #List
SET EmailSent = 1
WHERE id = @MinID
SELECT @MinID = @MinID + 1
END
DROP TABLE #List
END
So, I am sure if anyone is still awake, there will be maybe more info needed, but I did not want to put too much in one post. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 15, 2007 at 2:14 pm
Hi Greg;
remember that a trigger needs to just handle data, and not do business logic like sending an email...an email might take 5 seconds to actually finish doing the handshake and email to a distant server somewhere, and because of that, you should handle emails as a scheduled job that runs every minute or so.
also consider this: something gets entered on a monday, and no new data is entered for 10 days; becuase the TRIGGER executes only when someone inserts or updates, the email that should have gone out on day 5 doesn't go out until there is finally another event in the table...you want a schedule job to check constantly, or at least once a day, not ever time a record is inserted.
typically,this is what i do:
i create a table with all the fields required to send an email(subject, destination address, htmlbody,plaintextbody, everything else., and an additional bit column for SentSuccessfully.
I create a job that goes thru that table every minute, and selects all rows where SentSuccessfully=0
if the email process fails, which is possible due to DNS issues, mailbox full, all sorts of other issues, the SentSuccessfully flag is not updated, and would be re-attempted ont he next run of the job.
the advantage of this is the code for sending ALL emails is now centralized, instead of in each trigger you might want to send from.
I would avoid the trigger you were using, and again, have a sceduled job that checks the table once a day, or once an hour or something, and if it finds items that were not handled int eh last 5 days, simply adds a record to the EmailOut table.
if you need a real working example, let me know and I'll paste it, but it's much better than locking the table up while the trigger tries to send an email....and i don't think your trigger would handle multiple rows gracefully at this time.
Lowell
October 15, 2007 at 5:02 pm
Excellent suggestion Lowell!
The EmailOut table is the way to go. You don't need a trigger for this solution.
"--I have a job that runs every night to set the bit fields of my tblTest.
--It calculates the number of working days(thanks to Jeff Moden) since
--the entered_dt field value, where the resolove_dt field is null. So, in theory
--only *one* of the bit fields will get set to 1 on any given night, starting
--with FiveDayFlag, then Ten and Twenty. No problems here."
The job you have here is the one which determines which records need to be emailed. Just modify the job and add step that adds an entry to the EmailOut table.
October 15, 2007 at 5:18 pm
You need trigger to populate that table.
_____________
Code for TallyGenerator
October 15, 2007 at 5:58 pm
Sergiy (10/15/2007)
You need trigger to populate that table.
i disagree that a trigger is needed; part of the scheduled job should be doing a query like this:
select * from tbltest where getdate() - entered_dt >= 5 and resolve_dt is null
based on those results, he could update his column and of course populate the EmailOut table
and he could get rid of that step by making the flags calculated columns instead:
create table tblTest
(
ID INT IDENTITY(1,1),
EWO VARCHAR(20) UNIQUE NOT NULL,
ENTERED_DT SMALLDATETIME DEFAULT GETDATE(),
ENTERED_BY VARCHAR(30) DEFAULT SYSTEM_USER,
RESOLVE_DT SMALLDATETIME,
FiveDayFlag AS CASE WHEN GETDATE() - ENTERED_DT >= 5 then 1 else 0 end,
TenDayFlagAS CASE WHEN GETDATE() - ENTERED_DT >= 10 then 1 else 0 end,
TwentyDayFlagAS CASE WHEN GETDATE() - ENTERED_DT >= 20 then 1 else 0 end
)
Lowell
October 15, 2007 at 6:34 pm
Thanks everyone for the suggestions. Lowell, I think I can wrap my head around what you are suggesting. I guess I had reservations about making a table just to track the emails. As this case is somewhat unique with respect to the emails I send in that the message involves a variable. I am not sure what you mean by making the bit fields calculated columns, I did not know that was possible. So assuming I can make them as you say, what would be the trigger for them to update when getdate()-entered_dt >= 5, or 10, or whatever I want the value to be? Say I enter a record on 10/15. On 10/21 at at 12:00 am what happens to the table so that my calculated column "knows" it is time to change from 0 to 1? I am sorry if I am missing something, but this is sort of unfamiliar ground, so I made it work the only way I knew how. And the trigger as posted does handle multiple lines, it just takes around 2 seconds per email sent. Also, it seems I would need some way to tell whether the email sent was for 5 days, 10, or twenty.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 15, 2007 at 6:55 pm
well, here's what i would suggest; instead of tracking 5/10/20 day, lets keep the emailID of the email you sent because somethign was 5 days old.
[font="Courier New"]
CREATE TABLE tblTest
(
ID INT IDENTITY(1,1),
EWO VARCHAR(20) UNIQUE NOT NULL,
ENTERED_DT SMALLDATETIME DEFAULT GETDATE(),
ENTERED_BY VARCHAR(30) DEFAULT SYSTEM_USER,
RESOLVE_DT SMALLDATETIME,
ResolutionPeriod As ENTERED_DT - RESOLVE_DT,
FiveDayEmailID INT DEFAULT(0),
TenDayEmailID INT DEFAULT(0),
TwentyDayEmailID INT DEFAULT(0)
)[/font]--EMAILOUT TABLE; tweak this to the fields you need for your SMTP dll, let me know if you need one
[font="Courier New"]
CREATE TABLE EMAILOUT(
EMAILID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
FromEmailAddress varchar(100), --notifications@yourdomain.com
FromDisplayName varchar(100), --Notification Department
AuthenticationUserName varchar(100),
AuthenticationPassWord varchar(100),
ReplyToAddress varchar(100), --reply2@yourdomain.com
ToEmailAddress varchar(500), --comma delimited list
CCEmailAddress varchar(500), --comma delimited list
BCCEmailAddress varchar(500), --comma delimited list,
EmailPriority varchar(10) default 'NORMAL' CHECK (EmailPriority in('LOW','NORMAL','HIGH')),
EmailSubject varchar(1200),
EmailType varchar(12) default 'text/plain' CHECK (EmailType in('text/plain','text/html') ),
EmailBody varchar(4000) ,
EmailBodyFromFile varchar(250), --c:\filename.txt instead of from this table
AttachmentList varchar(250), --semicolon delimited list of files to attach,
SMTPPort int default 25, --port number
SentSuccessfully bit default 0
)[/font]
A scheduled job does these two steps:
select getdate() - entered_dt As DaysUnResolved,* from tbltest where getdate() - entered_dt >= 5 and resolve_dt is null
because we want to keep track of the actual EmailID, i can't think of anything to do except a RBAR solution:
Cursor for each row in the selection
--construct the subject dynamically for "5/10/20 day warning" and email body with a if case statement:DaysUnResolved >20,DaysUnResolved>10,else
case
insert into emailOut....
select @emailID = scope_identity()
update tblTest set FiveDayEmailID =@emailID
so this job does nothing except create an email to be sent, and tracks the email id
a different job does a cursor for SELECT * FROM EMAILOUT WHERE SENTSUCCESSFULLY=0,
if the email fails, nothing, but it would update SENTSUCCESSFULLY=1 if no error.
the big advantage is, as soon as you start sending emails out, someone will say "well can't you send an email if THIS happens too?"
since you'd have th structure already in place, you can just have another process add rows to the same emailout table. I know, I already went thru this.
hope this helps a bit.
Lowell
October 15, 2007 at 7:07 pm
also to follow up on this part of your question:
Say I enter a record on 10/15. On 10/21 at at 12:00 am what happens to the table so that my calculated column "knows" it is time to change from 0 to 1? I am sorry if I am missing something, but this is sort of unfamiliar ground, so I made it work the only way I knew how. And the trigger as posted does handle
nothing would magically know, only a query being run frequently could test for this condition. that's the advantage of the scheduled job, it would check if any records match the condition, say every 4 hours. Or Every minute. the frequency is up to you, depending on urgency. you may end up needing a preliminary notifications if something is not handled int he 1st 24 hours, it's up to the business decisions on that peice.
when the query is run, and it finds those rows, it would add the notifications we discussed automatically...a one time setup costs you a few hours, followed by automation...only time it breaks is when you turn off the SQL Server Agent or accidentally change the password.
Lowell
October 15, 2007 at 7:19 pm
Thanks again Lowell. That is a lot to chew on, especially for my home pc and SQL express. I will have to wait until I get back to work tomorrow to really make any headway. I guess I need to really think about creating an email table. It seems having the variables used for constructing the email stored in a permanent table is the only way to get around doing the work in the trigger, which I see will cause me headaches in the future.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 15, 2007 at 7:20 pm
Lowell (10/15/2007)
Sergiy (10/15/2007)
You need trigger to populate that table.i disagree that a trigger is needed; part of the scheduled job should be doing a query like this:
select * from tbltest where getdate() - entered_dt >= 5 and resolve_dt is null
)
It's another table/index seek/scan.
You don't need this overhead.
Trigger already has all records you need in "inserted".
No need to select them again.
Just drop them into "queue" table.
_____________
Code for TallyGenerator
October 15, 2007 at 7:26 pm
I think that is where I am heading Sergiy. Instead of populating the temp table in the trigger I will only use the trigger to populate a permanent queue table as you called it. When I tested the trigger without the email part, it took no time at all to populate my temp table.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 15, 2007 at 8:46 pm
Sergiy (10/15/2007)
Lowell (10/15/2007)
Sergiy (10/15/2007)
You need trigger to populate that table.i disagree that a trigger is needed; part of the scheduled job should be doing a query like this:
select * from tbltest where getdate() - entered_dt >= 5 and resolve_dt is null
)
It's another table/index seek/scan.
You don't need this overhead.
Trigger already has all records you need in "inserted".
No need to select them again.
Just drop them into "queue" table.
THE TRIGGER IS NOT NEEDED...
FIRST, why would you use the trigger, do you need to email the users on realtime? or at the moment the update is done? I don't think a 5-day, 10-day, 20day flag would require real time reponses. If it were a 5-min, 10-min, and a 20-min flag, then i might consider it.
SECOND, when is the trigger fired? On every update. But look closely, Sergiy. The email sending part filters
"FROM #List WHERE MessageNumber IS NOT NULL"
and the #List is created with
"MessageNumber = --This will be the number of days past entered_date
CASE WHEN d.FiveDayFlag = 0 AND i.FiveDayFlag = 1 THEN 5
WHEN d.TenDayFlag = 0 AND i.TenDayFlag = 1 THEN 10
WHEN d.TwentyDayFlag = 0 AND i.TwentyDayFlag = 1 THEN 20
ELSE NULL
END"
-- **** note that the "i" alias here refers to the "inserted" table.
Now what does this tell you? This means that the email entries only occur when the any of the flags are updated.
And just when are these flags updated??
"
--I have a job that runs every night to set the bit fields of my tblTest.
--It calculates the number of working days(thanks to Jeff Moden) since
--the entered_dt field value, where the resolove_dt field is null. So, in theory
--only *one* of the bit fields will get set to 1 on any given night, starting
--with FiveDayFlag, then Ten and Twenty. No problems here.
"
In reality, the emails here are sent every night when the job inspired by Jeff Modem (Hey Jeff, howdy? :D) runs and updates the test table.
So, as I was saying, You don't need a trigger for this solution.
The job you have here is the one which determines which records need to be emailed. Just modify the job and add step that adds an entry to the EmailOut table.
October 15, 2007 at 9:13 pm
Ronald,
look closer.
What we've got.
There are 3 "Flag" columns. And there is some job which updates those flags anyway.
And there is a trigger which captures updates and sends emails.
So, easiest and quickest way to go is just to exclude "send" part from the trigger and replace it with INSERT INTO statement.
When and how they intend to send emails - it's another question. Depending on the requirements it could be a next step in the same job, another job, some call from application, etc.
Your way will work as well, and if we would talk about new development it could be better option (not the best anyway, I would not implement neither of the options mentioned in this topic, but it's another story).
But taking into consideration starting point and trying to reduce number of modifications I would go with trigger.
_____________
Code for TallyGenerator
October 15, 2007 at 9:21 pm
Sergiy you've got a lot of experience; if you were starting from scratch, how would you handle an email notification requirement?
I'm sure I have developer tunnel vision with an email table and a schedule job, so because of that
i can see that adding via a trigger is good for instant notifications, the typical stuff you see like here on SSC forums and stuff. same for new logins, password changes and that kind of stuff.
Thanks!
Lowell
October 15, 2007 at 9:29 pm
SECOND, when is the trigger fired? On every update. But look closely, Sergiy. The email sending part filters
BINGO Ronald. The table actually has around 25 columns, but I am only concerned with the three bits, that get updated by a job at night. When updates happen that do not cause the conditions in the trigger, it just fires but nothing happens. So, theoretically, the only time the trigger will actually do any work to send the emails will be directly upon the nightly update script.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply