November 29, 2007 at 4:33 am
Hi there
I have a job table. Whenever the status changed from a job to planned an email should be send to the planner.
Somebody ruled out notification services. So I guess I have to create a trigger.
Never done this before. It should only send an email when that one column is changed.
It a database for Navision.
How should I create trigger to send email on update column Status ?
Many thx
El Jefe
JV
November 29, 2007 at 4:59 am
You can use the UPDATE() or COLUMNS_UPDATED in trigger bodies to find out if a particular column has been updated.
You can see a full example in Books Online http://msdn2.microsoft.com/en-us/library/ms187326.aspx
It would basically look like:
CREATE TRIGGER ...
IF UPDATE(Status )
BEGIN
...
END
Regards,
Andras
November 29, 2007 at 5:05 am
Andras's example of the trigger determining what changed is perfect...but you want to send the email outside of the trigger code, and not inside it.
remember that a trigger needs to just handle data, and not do business logic like sending an email(or create PDF Invoices, or really anything except move data)
...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; because 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.
now you can use the same process for events that occur in multiple data locations...you can add notifications when a new contact gets added, or an invoice hasnt been updated to "shipped" after 5 days, or whatever, simply by adding a new record toy 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.
there's a thread where we discussed this previously with someone else HERE that might be good for you as well:
http://www.sqlservercentral.com/Forums/Topic411008-8-1.aspx
Lowell
November 29, 2007 at 5:58 am
Hi Lowell
Thx for your quote.
But this is a Navision SQL database. So I cannot change the structure (mailsent flag). The table job contains projects. Is not the SQL job table !
Can u use the first tip then ?
Thx
El Jefe
JV
November 29, 2007 at 6:33 am
Hi
I guess I've created the trigger.
Next problem is that SQL Mail does not work with the 64bit version :angry:
Any clue how to get around this one ?
Thx in advance
El Jefe
JV
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply